Does db2 have an equivalent to mysql UNIX_TIMESTAMP()?

i.e the number of seconds since 1970-01-01 00:00:00 returned as a number


Thanks

There's no such function, but you could use something like:

I wrote this function a while ago:
Apparently the timestampdiff function is a bit buggy, as the following would seem to bare out:

values(timestampdiff(2, char(current timestamp - timestamp('1970-01-01-00.00.00'))), epoch(current timestamp))

1 2
----------- -----------
1089106733 1089970733

1 record(s) selected.

One of the above has got to be wrong!

Damian

TIMESTAMPDIFF is the one which is not right ...

This is documented ...

From version 8 SQL Reference :

The following assumptions may be used in estimating a difference:

There are 365 days in a year.
There are 30 days in a month.
There are 24 hours in a day.
There are 60 minutes in an hour.
There are 60 seconds in a minute.
These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

Cheers
Sathyaram

That's right, TIMESTAMPDIFF is an approximation. However, whether you can use it or not depends on the actual requirements. In some cases that approximation may work; if you really need the underlying OS time value then you can't rely on DB2 in any case - you've got to go all the way to the OS to get your number of seconds, e.g. by writing a UDF that does a system call...

Damian thanks for the point in the right direction.

The following statement returns that exact same as MySQL UNIX_TIMESTAMP

CAST (DAYS(CURRENT TIMESTAMP) - DAYS('1970-01-01') AS INTEGER) * 86400 + (MIDNIGHT_SECONDS(CURRENT TIMESTAMP – CURRENT TIMEZONE))

This will return the number of seconds since 1970-01-01 at the time of execution.