Developer's Guide to Time-Based SQL Queries in Oracle

In the world of databases, dates hold secrets waiting to be unlocked. Welcome to an exploration of time-based SQL queries in Oracle 11g. We'll journey through different scenarios, each accompanied by its corresponding queries, all centered around date columns in tables.

Our queries have been put to the test, so let's dive in and master the art of time-based querying!

*Note :- In toad please execute the query without (;)

Table structure :-

CREATE TABLE orders
  (
     order_id   NUMBER,
     order_date DATE
  );

• INSERT INTO orders (order_id, order_date) VALUES (1, To_date('2023-07-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO orders (order_id, order_date) VALUES (2, To_date('2023-07-15 13:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO orders (order_id, order_date) VALUES(3, To_date('2023-07-31 16:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO order (order_id, order_date) VALUES (4, To_date('2023-08-01 18:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO orders (order_id, order_date) VALUES(5, To_date('2023-08-15 19:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO orders (order_id, order_date) VALUES(6, To_date('2023-07-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));
• INSERT INTO orders (order_id, order_date) VALUES (7, To_date('2023-07-15 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

Select queries :-

Querying Data of July 2023: Mastering Month-Based Filtering

- Extracting July 2023 Records with EXTRACT:

SELECT *
FROM   orders
WHERE  Extract(month FROM order_date) = 7
       AND Extract(year FROM order_date) = 2023; 

- Querying with TO_CHAR:

SELECT *
FROM   orders
WHERE  To_char(order_date, 'MM') = '07'
       AND To_char(order_date, 'YYYY') = '2023';

- Filtering with BETWEEN:

SELECT *
FROM   orders
WHERE  order_date BETWEEN To_date('2023-07-01', 'YYYY-MM-DD') AND
                          To_date('2023-07-31', 'YYYY-MM-DD');

- Precision with TRUNC and INTERVAL:

SELECT *
FROM   orders
WHERE  Trunc(order_date, 'MM') = To_date('2023-07-01', 'YYYY-MM-DD')
       AND Trunc(order_date, 'MM') + interval '1' month - interval '1' day =
           To_date(
           '2023-07-31', 'YYYY-MM-DD');

- Date Range Filtering:

SELECT *
FROM   orders
WHERE  order_date >= To_date('2023-07-01', 'YYYY-MM-DD')
       AND order_date < To_date('2023-08-01', 'YYYY-MM-DD');

Isolating Data for July 15, 2023: Day-Level Analysis

• Using TRUNC for a Specific Day:

SELECT *
FROM   orders
WHERE  Trunc(order_date) = To_date('2023-07-15', 'YYYY-MM-DD');

• Extracting Day, Month, and Year:

SELECT *
FROM   orders
WHERE  Extract(year FROM order_date) = 2023
       AND Extract(month FROM order_date) = 7
       AND Extract(day FROM order_date) = 15;

• Matching with TO_CHAR:

SELECT *
FROM   orders
WHERE  To_char(order_date, 'YYYY-MM-DD') = '2023-07-15';

Analyzing Data on July 15, 2023, from 1 PM to 4 PM: Time Window Insights

• Filtering with TRUNC and TO_NUMBER:

SELECT *
FROM   orders
WHERE  Trunc(order_date) = To_date('2023-07-15', 'YYYY-MM-DD')
       AND To_number(To_char(order_date, 'HH24')) BETWEEN 13 AND 16;

• Leveraging Date-Time Comparison:

SELECT *
FROM   orders
WHERE  order_date >= To_date('2023-07-15 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND order_date < To_date('2023-07-15 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

• Time Interval with TO_DATE and AM/PM:

SELECT *
FROM   orders
WHERE  order_date >= To_date('2023-07-15 01:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
       AND order_date < To_date('2023-07-15 04:00:00 PM',
                        'YYYY-MM-DD HH:MI:SS AM');

Grouping Records by Hour

• How can I group records by hour on August 2, 2023, specifically from 9 am to 6 pm?

SELECT To_char(order_date, 'YYYY-MM-DD HH24') AS hour_group,
       Count(*)                               AS record_count
FROM   orders
WHERE  order_date >= To_date('2023-08-02 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND order_date < To_date('2023-08-02 19:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP  BY To_char(order_date, 'YYYY-MM-DD HH24')
ORDER  BY hour_group;