Search code examples
oracle12c

Column of type "timestamp(6) with timezone" and "current time" difference in minutes

I have Oracle 12c DB table and one of it's column utc_timestamp is of type

UTC_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE

It stores timestamp in UTC while current_timestamp and systimestamp both gives timestamp in different timezones.

How can I get time difference in MAX(utc_timestamp) and current_timestamp in minutes ignoring time difference due to different time zones.

For example:

select current_timestamp from dual;
Gives=> 23-AUG-17 04.43.16.253931000 PM AMERICA/CHICAGO

select systimestamp from dual;
Gives=> 23-AUG-17 05.43.16.253925000 PM -04:00

select max(UTC_TIMESTAMP) from table_name;
Gives=> 23-AUG-17 09.40.02.000000000 PM +00:00

For above condition when I run SQL to check time difference between in MAX(utc_timestamp) and current_timestamp I should get number 3.

I think I need something like:

select (extract(minute from current_timestamp) - extract(minute from max(UTC_TIMESTAMP)) * 1440) AS minutesBetween from table_name;

But different timezones are messing it up and I get negative number like -4317. This might be correct as current_timestamp will be higher than max(utc_timestamp) being in CST. So I tried:

select (extract(minute from CAST(current_timestamp as TIMESTAMP(6) WITH TIME ZONE)) - extract(minute from max(UTC_TIMESTAMP)) * 1440) AS minutesBetween from table_name;

This SQL runs without error but producing a big negative number like -83461. Please help me find what am I doing wrong.

Solution

  • You really have two problems here.

    One is to convert CURRENT_TIMESTAMP to UTC. That is trivial:

    select CURRENT_TIMESTAMP AT TIME ZONE 'UTC' from dual   [.....]

    (use the AT TIME ZONE clause https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1007699)

    The other is that the difference between two timestamps is an interval, not a number.

    select current_timestamp at time zone 'UTC'
       - to_timestamp_tz('24-AUG-17 04.00.00.000 AM UTC', 'dd-MON-yy hh.mi.ss.ff AM TZR')
from dual;

    produces something like 

    +00 00:02:39.366000

    which means + (positive difference) 00 days, 00 hours, 02 minutes, 39.366 seconds.

    If you just want the minutes (always rounded down), you may wrap this whole expression within extract( minute from < ...... > ). Be aware though that the answer will still be 2 (minutes) even if the difference is five hours and two minutes. It is probably best to leave the result in interval data type, unless you are 100% sure (or more) that the result is always less than 1 hour.