CODEWITHSUNDEEP
db2
JDPeckham
JDPeckham

Reputation: 2524

How would you convert a UTC date to an epoch in DB2?

I can do this to get UTC

select CURRENT TIMESTAMP - CURRENT TIMEZONE AS utc 
FROM SYSIBM.SYSDUMMY1;

one would imagine something like

select GetEpoch(CURRENT TIMESTAMP - CURRENT TIMEZONE) AS utc
FROM SYSIBM.SYSDUMMY1;

what would be the logic for GetEpoch?

Upvotes: 0

Views: 2180

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

The following query returns the same result in both rows:

VALUES 
  extract(epoch from current timestamp)
, (days(current timestamp) - days(timestamp('1970-01-01'))) * 86400  + midnight_seconds(current timestamp)

Upvotes: 0

mao
mao

Reputation: 12267

If your Db2-database lives either on Linux/Unix/Windows or i-series and is currently supported by IBM, then you can use the EXTRACT function which accepts an EPOCH parameter (among other things).

For EPOCH, Extract Returns The number of seconds since 1970-01-01 00:00:00.00 for the supplied date/timestamp expression.

Example values extract(epoch from current timestamp) or select extract(epoch from current timestamp) from sysibm.sysdummy1

Db2-LUW documentation here.

Db2-for-i documentation here.

Although the EXTRACT function is available on Db2-for-Z/OS, it does not appear to accept the EPOCH parameter, unless undocumented, documentation here.

Upvotes: 1

Related Questions