Closest date/time to top of hour-CodePudding

I have a table with normal DATE field in it:

5/9/2022 2:32:02 AM
5/9/2022 12:33:02 PM
5/9/2022 10:34:02 AM
5/9/2022 10:20:02 AM
5/9/2022 6:54:02 AM
5/9/2022 4:28:02 AM
5/9/2022 5:08:02 AM

I need a query that I can run that will give me the record that is the closest to the top of the hour I am asking for. So like, which is closest to 5:00am, or 7:00am, or 12:00pm.

Thanks!

CodePudding user response：

A query like the one below can be used to show each record that is closest to the "top of the hour". In your sample data, none of the records overlap to the nearest "top of the hour", so all the records will be shown. If you add additional dates that are closer together to the top of the hour, then only the the dates closest to the "top of the hour" will be shown.

Query

WITH
    sample_dates (dt)
    AS
        (SELECT TO_DATE ('5/9/2022 2:32:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 12:33:02 PM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 10:34:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 10:20:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 6:54:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 4:28:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL
         UNION ALL
         SELECT TO_DATE ('5/9/2022 5:08:02 AM', 'MM/DD/YYYY HH:MI:SS PM') FROM DUAL)
  SELECT TO_CHAR (dt, 'MM/DD/YYYY HH:MI:SS PM')               AS dt,
         TO_CHAR (nearest_hour, 'MM/DD/YYYY HH:MI:SS PM')     AS nearest_hour
    FROM (SELECT dt,
                 nearest_hour,
                 ROW_NUMBER () OVER (PARTITION BY nearest_hour ORDER BY time_from_hour)    AS time_rank
            FROM (SELECT dt,
                         CASE
                             WHEN dt - TRUNC (dt, 'HH') <
                                  ABS ((TRUNC (dt, 'HH')   INTERVAL '1' HOUR) - dt)
                             THEN
                                 TRUNC (dt, 'HH')
                             ELSE
                                 TRUNC (dt, 'HH')   INTERVAL '1' HOUR
                         END                                                          AS nearest_hour,
                         LEAST (dt - TRUNC (dt, 'HH'),
                                ABS ((TRUNC (dt, 'HH')   INTERVAL '1' HOUR) - dt))    AS time_from_hour
                    FROM sample_dates))
   WHERE time_rank = 1
ORDER BY 1;

Result

                       DT              NEAREST_HOUR
_________________________ _________________________
05/09/2022 02:32:02 AM    05/09/2022 03:00:00 AM
05/09/2022 04:28:02 AM    05/09/2022 04:00:00 AM
05/09/2022 05:08:02 AM    05/09/2022 05:00:00 AM
05/09/2022 06:54:02 AM    05/09/2022 07:00:00 AM
05/09/2022 10:20:02 AM    05/09/2022 10:00:00 AM
05/09/2022 10:34:02 AM    05/09/2022 11:00:00 AM
05/09/2022 12:33:02 PM    05/09/2022 01:00:00 PM

CodePudding user response：

To get the closest row to a particular hour on any day then, from Oracle 12, you can use:

SELECT *
FROM   table_name
ORDER BY ABS(
           TRUNC(yourDate)   INTERVAL '07:00:00' HOUR TO SECOND
           - yourDate
         )
FETCH FIRST ROW ONLY;

Which, for the sample data:

CREATE TABLE table_name (id, yourDate) AS
SELECT 1, DATE '2022-05-09'   INTERVAL '02:32:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2022-05-09'   INTERVAL '12:33:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, DATE '2022-05-09'   INTERVAL '10:34:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 4, DATE '2022-05-09'   INTERVAL '10:20:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-05-09'   INTERVAL '06:54:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 6, DATE '2022-05-09'   INTERVAL '04:28:02' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 7, DATE '2022-05-09'   INTERVAL '05:08:02' HOUR TO SECOND FROM DUAL;

Outputs:

ID YOURDATE

5 2022-05-09 06:54:02

ID	YOURDATE
5	2022-05-09 06:54:02

db<>fiddle here