Search code examples
oracle-databaseplsql

Retrieve record specific date and time in Oracle SQL

I want to retrieve the data from an Oracle datetime column from

    26/03/2024 11:00:00. AM +00:00

to

    27/03/2024 03:00:00. AM +00:00

as one day record for 26/03/2024 using sysdate.

Can you please help me to write the SQL query in Oracle to search by date and time?

I want get the results what ever sold detail current date 11 am until next date 3 am as one record.

Expected results:

desired output

Example:

Example

Solution

  • Use some date math. If you subtract 3 hours and 1 second from your TRANSACTION_DATE you will fit the data into same day.

    WITH    --  S a m p l e    D a t a :
    tbl AS
        ( Select  To_Date('26/03/2024 11:00:00', 'dd/mm/yyyy hh24:mi:ss') "TRANSACTION_DATE", 2 "QTY", 50 "AMOUNT" From Dual Union All
          Select  To_Date('27/03/2024 03:00:00', 'dd/mm/yyyy hh24:mi:ss') "TRANSACTION_DATE", 2 "QTY", 50 "AMOUNT" From Dual Union All
          Select  To_Date('27/03/2024 11:00:00', 'dd/mm/yyyy hh24:mi:ss') "TRANSACTION_DATE", 2 "QTY", 50 "AMOUNT" From Dual Union All
          Select  To_Date('28/03/2024 03:00:00', 'dd/mm/yyyy hh24:mi:ss') "TRANSACTION_DATE", 2 "QTY", 50 "AMOUNT" From Dual 
        )

    --    S Q L :
Select   TRUNC(TRANSACTION_DATE + NUMTODSINTERVAL( -3, 'Hour') + NUMTODSINTERVAL( -1, 'Second')) "A_DATE", 
         Sum(QTY) "QTY", Sum(AMOUNT) "AMOUNT"
From     tbl
Group By TRUNC(TRANSACTION_DATE + NUMTODSINTERVAL( -3, 'Hour') + NUMTODSINTERVAL( -1, 'Second'))
Order By TRUNC(TRANSACTION_DATE + NUMTODSINTERVAL( -3, 'Hour') + NUMTODSINTERVAL( -1, 'Second'))

    /*    R e s u l t :
A_DATE          QTY     AMOUNT
-------- ---------- ----------
26.03.24          4        100
27.03.24          4        100    */