Topic
  • No replies
ChuanKaiChen
ChuanKaiChen
10 Posts

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?