• No replies
10 Posts

Pinned topic How to get UTC timestamp like oracle SYS_EXTRACT_UTC

‏2013-08-17T10:48:01Z | db2 migration oracle utc

In DB2, we use the following sql to get UTC time:
"select current timestamp - current timezone from sysibm.sysdummy1"
In Oracle COMPATIBILITY mode, the result is not correct:
db2inst2@linux:~> db2 "select current timestamp , current timezone, current timestamp - current timezone from sysibm.sysdummy1"

1                          2        3
-------------------------- -------- --------------------------
2012-06-17-   53000. 1867-05-09-

You can see that the timezone 53000 is treated as days in Oracle COMPATIBILITY DB.

In Oracle, the customer use the following function to get UTC time:
But this function SYS_EXTRACT_UTC has not been implemented in DB2 V9.7 FP8.


I developed a sql to get UTC time:

    CURRENT timestamp
    - TRUNC((current timezone/10000),0) hour
    - TRUNC((current timezone/100) - (TRUNC((current timezone/10000),0) * 100),0) minutes
    - ((current timezone) - TRUNC(current timezone/100,0)*100) seconds 

But it's ugly.

Do you have any better idea to get UTC timestamp?