Search code examples
sqltimestampibm-midrangeutcdb2-400

How can I get a UTC timestamp in SQL400 on iSeries?

Our iSeries (aka AS400, aka i5, aka SystemI) runs in local time, but we have an application, that uses UTC (written in Java, connected by JDBC to the iSeries DB).

How can I generate UTC-timestamps via SQL on that system?

Solution

  • DB2/400 (which likes to be different in details from other DB2 flavours), has a special register called CURRENT TIMEZONE. It contains a numeric value, that can be subtracted from a timestamp or time value.

    In an example query:

    SELECT 
    CURRENT TIMESTAMP AS local, 
    CURRENT TIMEZONE AS offset, 
    CURRENT TIMESTAMP - CURRENT TIMEZONE AS utc
FROM SYSIBM.SYSDUMMY1

    Gives you something like:

    +----------------------------+--------+----------------------------+
| LOCAL                      | OFFSET | UTC                        |
+----------------------------+--------+----------------------------+
| 2014-05-27 14:09:19.127339 | 20.000 | 2014-05-27 12:09:19.127339 |
+----------------------------+--------+----------------------------+

    Notes:

    • This example has an offset of +2 hours for central European summer time.
    • SYSIBM.SYSDUMMY1 is a special table supplied by IBM, with the sole purpose of having exactly one row with exactly one column.
    • Things like NOW() - CURRENT TIMEZONE and CURRENT TIME - CURRENT TIMEZONE work as well.
    • Reference in IBM's iSeries information center