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.
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.
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 )