Database administration for fun and profit

Unix time from DB2

Here’s one way to obtain the Unix time value (the number of seconds since midnight on January 1, 1970) from a DB2 timestamp:

  (days(current_timestamp-current_timezone) - days('1970-01-01') )*86400 + 
  midnight_seconds(current_timestamp - current_timezone)

This can be used in a query directly or wrapped into a simple SQL user-defined function.

2 Responses to Unix time from DB2

  1. nick says:

    NOTE: this does not work correctly if your DB2 9.7 database is in the Oracle compatibility mode. Date arithmetic are implemented differently to be compatible with Oracle. I’m going to post a new solution for the Oracle compatibility mode shortly.

  2. nick says:

    Here’s the version for an Oracle-compatible DB2 database:

     - current_timezone /240000 
     - mod(current_timezone,10000)/144000) 
     - days('1970-01-01')
    + midnight_seconds(
     - current_timezone /240000 
     - mod(current_timezone, 10000)/144000

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.