Difference between timestamps captured in hourly timeblocks

Question

I would like to capture the difference between two timestamps, with the output displayed in minutes per 24 hr time block.

E.g.

DATA- START - 01 May 2014 23:56:00 ---- END-02 May 2014 01:04:00

OUTPUT REQUIRED E.G- 23hr -4mins, 0 hr - 60 mins ; 1hr- 4 mins..

I have managed to calculate the similar results to the above Output above by creating case statements for each 24 hr timeblock, however not all time is captured i.e. particularly for some differences across 2 calendar days. The maximum difference between timestamps will be 23 hrs and 59mins.

This is being undertaken via oracle discoverer. Any advice here would be most appreciated.

Is there a maximum number of days the dates could cover (these are dates rather than timestamps right?) If it's 3 do you want 3 rows or 3 columns? If it's n days you understand you can't put it in columns easily? — Ben, Jun 16 '14 at 11:36
Hi Thanks for the prompt response. These are timestamps. The maximum difference between any two for comparison will be < 24 hrs. This is being output at a transactional level running down as rows with the 24 hr time blocks as columns. I hope this is seems to make sense. — mlee, Jun 16 '14 at 11:41
I think this is a [duplicate](http://stackoverflow.com/questions/23816576/oracle-query-to-group-date-difference-by-hour/23820159#23820159). — Bob, Jun 17 '14 at 11:37

score 0 · Answer 1 · edited May 23 '17 at 11:49

If they're TIMESTAMPs then you can use the fact that TIMESTAMPs arithmetic results in an INTERVAL DAY TO SECOND data type. Given something over 2 days, TRUNC(<end_date>) will provide a date of midnight, on the day in between.

SQL> with the_data as (
  2   select to_timestamp('2014-05-01 23:56:00','yyyy-mm-dd hh24:mi:ss') as s
  3        , to_timestamp('2014-05-02 01:04:00','yyyy-mm-dd hh24:mi:ss') as e
  4     from dual
  5    union
  6   select to_timestamp('2014-05-01 23:56:00','yyyy-mm-dd hh24:mi:ss') as s
  7        , to_timestamp('2014-05-01 23:57:00','yyyy-mm-dd hh24:mi:ss') as e
  8     from dual
  9          )
 10  select case when trunc(e) = trunc(s) then e - s
 11              else trunc(e) - s
 12         end as day1
 13       , case when trunc(e) = trunc(s) then null
 14              else e - trunc(e)
 15         end as day2
 16    from the_data;

DAY1                           DAY2
------------------------------ ------------------------------
+000000000 00:01:00.000000000
+000000000 00:04:00.000000000  +000000000 01:04:00.000000000

You need the CASE statement because you need to do different things, depending on whether your timestamps are on the same day or not. If your datatype is actually a DATE, then you'll end up with fractions of days as the returned data type

SQL> with the_data as (
  2   select to_date('2014-05-01 23:56:00','yyyy-mm-dd hh24:mi:ss') as s
  3        , to_date('2014-05-02 01:04:00','yyyy-mm-dd hh24:mi:ss') as e
  4     from dual
  5    union
  6   select to_date('2014-05-01 23:56:00','yyyy-mm-dd hh24:mi:ss') as s
  7        , to_date('2014-05-01 23:57:00','yyyy-mm-dd hh24:mi:ss') as e
  8     from dual
  9          )
 10  select case when trunc(e) = trunc(s) then e - s
 11              else trunc(e) - s
 12         end as day1
 13       , case when trunc(e) = trunc(s) then null
 14              else e - trunc(e)
 15         end as day2
 16    from the_data;

      DAY1       DAY2
---------- ----------
0.00069444
0.00277777 0.04444444

You can then turn this into the number of hours and minutes by adding this fraction to the current SYSTIMESTAMP and then removing it afterwards.

select systimestamp + case when trunc(e) = trunc(s) then e - s
                           else trunc(e) - s
                      end 
       - systimestamp as day1
     , systimestamp + case when trunc(e) = trunc(s) then null
                           else e - trunc(e)
                      end 
       - systimestamp
  from the_data;

This works because you're creating the correct number of minutes past the current timestamp and then removing it again. However, because TIMESTAMP arithmetic results in an INTERVAL DAY TO SECOND you've converted it into the correct format along the way.

It's always best to store data in the data-type most suited for it. If you then want to format this differently you can do so as you extract from the database.

Difference between timestamps captured in hourly timeblocks

1 Answers1