Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
No replies
ChuanKaiChen
ChuanKaiChen
10 Posts
ACCEPTED ANSWER

Pinned topic How to get UTC timestamp like oracle SYS_EXTRACT_UTC

‏2013-08-17T10:48:01Z |

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-09.21.34.985014   53000. 1867-05-09-09.21.34.985014

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:
SYS_EXTRACT_UTC(current_timestamp)
But this function SYS_EXTRACT_UTC has not been implemented in DB2 V9.7 FP8.

 

I developed a sql to get UTC time:

select
    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 
from SYSIBM.SYSDUMMY1

But it's ugly.

Do you have any better idea to get UTC timestamp?