Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: current timestamp in UTC

    Hello,

    Hi,
    I was looking for a DB2 functions which was equivlent of GetUTCdate() from SQL SERVER.

    I want to create a table with a timestamp column that has a default value of UTC timestamp.

    I tried:
    DbServerTime timestamp not null with DEFAULT current timestamp - current timezone

    but this is incorrect.

    Thank you.

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Considering your server timestamp in EST, you can try something like the following:

    >db2 "create table test_timestamp (
    Employee_Id smallint,
    TS_EST timestamp with default current timestamp,
    TS_UTC timestamp generated always as (TS_EST + 5 hours) )"

    >db2 "select employee_id, TS_UTC from test_timestamp"

    EMPLOYEE_ID TS_UTC
    ----------- ------- ----------------------
    3311 2011-02-16-02.15.18.532000

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    (deleted due to incorrect information)
    Last edited by Stealth_DBA; 02-15-11 at 14:57.

  4. #4
    Join Date
    Jan 2011
    Posts
    8
    Thanks for the suggestion; i will use it. Surprised that I cannot do it with a single column.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Adding 5 hours only works when Daylight Savings Time is not in effect.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2011
    Posts
    8
    agreed. that is why i have tried with current timestamp - current timezone; but that syntax will not work at the DDL level.

  7. #7
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    That's correct, + 5 hours willn't consider DST.

    What you can do is to add a field in the table with datatype timestamp (e.g. TS_UTC), and then write an AFTER INSERT Trigger on the same table, which will update the TS_UTC field for every insert with values derived from "current timestamp - current timezone".

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •