datori

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:

values(
  (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:

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

Leave a Reply

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