Oracle get 1hr back date

Question

In Oracle, I want to get specific intervals.

Consider, sysdate is 10-dec-2016 10:15:23 (dd-mon-yyyy hh24:mi:ss). I want to get a date period like 10-dec-2016 09:00:00 to 10-dec-2016 10:00:00

How to get this date period through query. If you have observed this date period is 1hr back from current HH24 format and rounded to 00:00. The query should work for whatever I set. Above date period is for 1hr. I should be able to set for any number of hours like 2hrs,3hrs etc.

Answer 1

Something like:

select trunc(your_date, 'hh') - number_of_hours_to_go_back/24 start_dt,
       trunc(your_date, 'hh') end_dt
from   dual;

if you need it in a SQL statement, or:

declare
  v_date date := to_date('10/12/2016 10:15:23', 'dd/mm/yyyy hh24:mi:ss');
  v_start_dt date;
  v_end_dt date;
  v_num_hours_back integer;
begin
  v_start_dt := trunc(v_date, 'hh') - v_num_hours_back/24;
  v_end_dt := trunc(v_date, 'hh');
end;
/

if you need it in PL/SQL (to save unnecessary context switching between SQL and PL/SQL).

Answer 2

Here is an example

WITH dt AS
  ( SELECT ROUND(sysdate,'HH') hour_date FROM dual
  )
SELECT hour_date - 1/24, hour_date FROM dt

1. Round date to nearest hour.
2. For oracle Date arithmetic, subtracting 1 from a DATE type is 24 hours, so 1/24 is one hour, etc.

Answer 3

This would be a generic function you can use for any interval which fits into "higher range"

CREATE OR REPLACE FUNCTION MakeInterval(ts IN TIMESTAMP, roundInterval IN INTERVAL DAY TO SECOND) RETURN TIMESTAMP DETERMINISTIC IS
    denom INTEGER;
BEGIN
    IF roundInterval >= INTERVAL '1' HOUR THEN
        denom := EXTRACT(HOUR FROM roundInterval);
        IF MOD(24, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts) + TRUNC(EXTRACT(HOUR FROM ts) / denom) * denom * INTERVAL '1' HOUR;
    ELSIF roundInterval >= INTERVAL '1' MINUTE THEN
        denom := EXTRACT(MINUTE FROM roundInterval);
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTE FROM ts) / denom) * denom * INTERVAL '1' MINUTE;
    ELSE
        denom := EXTRACT(SECOND FROM roundInterval);                
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECOND FROM ts) / denom) * denom * INTERVAL '1' SECOND;
    END IF;
END MakeInterval;


select 
   MakeInterval(TO_TIMESTAMP('10-dec-2016 10:15:23', 'dd-mon-yyyy hh24:mi:ss'), INTERVAL '1' HOUR) as time
from dual;

For other intervals use INTERVAL '2' HOUR or INTERVAL '30' MINUTE, etc.