CODEWITHSUNDEEP
db2unix-timestamp
iims
iims

Reputation: 313

converting db2 datestamp to Unix timestamp

How can i convert 2012-04-12 00:00:00 to a unix timestamp in DB2. is there any inbuild function available in sql s

Thank you.

Upvotes: 4

Views: 6492

Answers (2)

Stavr00
Stavr00

Reputation: 3314

Using the DAYS and MIDNIGHT_SECONDS is much more precise than TIMESTAMPDIFF:

SELECT
86400*(DAYS(CURRENT TIMESTAMP - CURRENT TIMEZONE)-DAYS('1970-01-01'))
+ MIDNIGHT_SECONDS(CURRENT TIMESTAMP - CURRENT TIMEZONE)
"UNIX_TIMESTAMP"
FROM SYSIBM.SYSDUMMY1

Upvotes: 5

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

By Unix timestamp I assume you mean the number of seconds (or whatever) since 1970-01-01 00:00:00 UTC.

There is no built in functionality for this in DB2 (as of V6R1).
You're also up against the following issues:

  • All timestamps in DB2 are 'local time' - they contain no timezone information, and all CURRENT_TIMESTAMP writes are based on what time the requesting system thinks it is, not the host.
  • Daylight savings time changes frequently. You would need to add overhead to manage this for your conversion.
  • The TIMESTAMPDIFF function returns an estimate, not an exact value. You could probably survive for the years/months durations, over sufficient differences, but days aren't likely to cut it.
  • Timestamp arithmetic is imprecise (among other things, months are assumed to be always 30 days in length...)

Your best bet will be to start using DAYS (which returns the number of days since 0001-01-01). Keep in mind you better do everything in UTC, because it will not take DST into account.

Upvotes: 3

Related Questions