Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: UTC date/time value in TIMESTAMP column

    Hi all,

    I'm having troubles storing the date/time data in UTC format in DB2.
    Database: DB2 9.7 on Windows 2003 Server. Timezone locale: Eastern Time with daylight savings.
    The data is received in file batches in UTC format.

    The problem is when I try to upload the UTC date/time into the TIMESTAMP column in DB2 it gets adjusted by DB2 automatically.

    example:
    VALUES ( TIMESTAMP('2010-03-14 02:15:00'));
    result: 3/14/2010 3:15:00 AM

    As you can see time gets adjusted by 1 hour since 2:00 am not a valid hour value for March 14, 2010. That's the point when daylight savings gets into effect.

    Is there any way to enforce DB2 to use a column with TIMESTAMP datatype as is or/and in UTC format?

    Any help will be greatly appreciated!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ept View Post
    column in DB2 it gets adjusted by DB2 automatically.

    example:
    VALUES ( TIMESTAMP('2010-03-14 02:15:00'));
    result: 3/14/2010 3:15:00 AM
    I'm not sure if you understand correctly what is happening. Example:

    Code:
    >db2 VALUES ( TIMESTAMP('2010-03-14 02:15:00'))
    
    1
    --------------------------
    2010-03-14-02.15.00.000000
    
      1 record(s) selected.
    DB2 does not adjust column values automatically. What you are seeing could be a result of the difference in locale settings between the DB2 server and your client.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by n_i View Post
    I'm not sure if you understand correctly what is happening. Example:

    Code:
    >db2 VALUES ( TIMESTAMP('2010-03-14 02:15:00'))
    
    1
    --------------------------
    2010-03-14-02.15.00.000000
    
      1 record(s) selected.
    DB2 does not adjust column values automatically. What you are seeing could be a result of the difference in locale settings between the DB2 server and your client.
    Timezone locale: Eastern Time with daylight savings

    Lenny

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lenny77 View Post
    Timezone locale: Eastern Time with daylight savings

    So?
    Code:
    >w32tm /tz
    Time zone: Current:TIME_ZONE_ID_DAYLIGHT Bias: 300min (UTC=LocalTime+Bias)
      [Standard Name:"Eastern Standard Time" Bias:0min Date:(M:11 D:1 DoW:0)]
      [Daylight Name:"Eastern Daylight Time" Bias:-60min Date:(M:3 D:2 DoW:0)]

  5. #5
    Join Date
    Oct 2009
    Posts
    6
    Thank you for your responses!

    Both the server and the client have EST timezone with a checked checkbox for "automatically adjust clock for daylight saving changes".
    Still confused on why the timestamp values are not displayed as they are stored in the database in certain cases...

    I created table with TIMESTAMP column having few values between "2010-03-14 02:00:00" to "2010-03-14 02:59:00"

    -- Table Code
    /*
    CREATE TABLE RSI.TEST_DATE_TIMESTAMP
    (
    REC_ID BIGINT NOT NULL
    GENERATED ALWAYS AS IDENTITY
    (START WITH 1
    ,INCREMENT BY 1
    ,NO MAXVALUE
    ,NO CYCLE)

    ,date_read DATE
    ,timestamp_read TIMESTAMP
    ,CONSTRAINT PK_TEST_DATE_TIMESTAMP_REC_ID PRIMARY KEY (REC_ID)
    )
    -- Table data
    INSERT INTO RSI.TEST_DATE_TIMESTAMP
    (
    date_read
    ,timestamp_read
    )
    VALUES ( DATE('2010-03-14 02:15:00'), TIMESTAMP('2010-03-14 02:15:00') )
    , ( DATE('2010-03-14 02:30:00'), TIMESTAMP('2010-03-14 02:30:00') )



    -- Query
    VALUES TIMESTAMP('2010-03-14 02:15:00')

    */


    --************************************************** ********************************************
    1) AQUA STUDIO IDE(3rd party client)
    --************************************************** ********************************************
    [TABLE]
    command: SELECT * FROM RSI.TEST_DATE_TIMESTAMP ->
    result:
    1 3/14/2010 3/14/2010 3:15:00 AM
    2 3/14/2010 3/14/2010 3:30:00 AM


    [QUERY]
    command: VALUES TIMESTAMP('2010-03-14 02:15:00') ->
    result:
    3/14/2010 3:15:00 AM


    [EXPORT TABLE]
    command: manual export of a table data to txt file using IDE
    result:
    "REC_ID","DATE_READ","TIMESTAMP_READ"
    1,"2010-03-14","2010-03-14 03:15:00"
    2,"2010-03-14","2010-03-14 03:30:00"



    [EXPORT QUERY]
    command: manual export of a query to txt file using IDE
    result:
    "1"
    "2010-03-14 03:15:00"


    --************************************************** ********************************************
    2) DB2 CONTROL CENTER CLIENT TOOL
    --************************************************** ********************************************
    [TABLE]
    command: SELECT * FROM RSI.TEST_DATE_TIMESTAMP ->
    result:
    1 3/14/2010 3/14/2010 3:15:00 AM
    2 3/14/2010 3/14/2010 3:30:00 AM

    [QUERY]
    command: VALUES TIMESTAMP('2010-03-14 02:15:00') ->
    resuls:
    3/14/2010 3:15:00 AM


    [EXPORT TABLE]
    command: EXPORT TO "C:\1\electra_controlcenter_table.txt" OF DEL MESSAGES "C:\Program Files\IBM\SQLLIB\1" SELECT * FROM RSI.TEST_DATE_TIMESTAMP!
    result:
    1,20100314,"2010-03-14-02.15.00.000000"
    2,20100314,"2010-03-14-02.30.00.000000"


    [EXPORT QUERY]
    command: EXPORT TO "C:\1\electra_controlcenter_query.txt" OF DEL MESSAGES "C:\Program Files\IBM\SQLLIB\1" SELECT TIMESTAMP('2010-03-14 02:15:00') FROM SYSIBM.SYSDUMMY1!
    result:
    "2010-03-14-02.15.00.000000"



    --************************************************** ********************************************
    3) DB2 COMMAND LINE TOOL
    --************************************************** ********************************************
    [TABLE]
    db2 => SELECT * FROM RSI.TEST_DATE_TIMESTAMP

    REC_ID DATE_READ TIMESTAMP_READ
    -------------------- ---------- --------------------------
    1 03/14/2010 2010-03-14-02.15.00.000000
    2 03/14/2010 2010-03-14-02.30.00.000000

    [QUERY]
    db2 => VALUES TIMESTAMP('2010-03-14 02:15:00')
    1
    --------------------------
    2010-03-14-02.15.00.000000

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ept View Post
    Thank you for your responses!

    Both the server and the client have EST timezone with a checked checkbox for "automatically adjust clock for daylight saving changes".
    Still confused on why the timestamp values are not displayed as they are stored in the database in certain cases...
    Not sure what you mean by "not displayed as they are stored in the database in certain cases".

    DB2 always stores date, time, and timestamp the same way, regardless of the territory code of the server or what format it is inserted with (assuming that DB2 understands the format and can translate it correctly). They are stored similar to packed decimal (but without the sign-half byte at the end). This means they are stored with 2 digits per byte. For example, a data is always stored as YYYYMMDD in 4 bytes.

    The way it is displayed (with dashes, commas, periods, etc) and the order of MONTH, DAY, YEAR, etc depends on the the territory code of your client and has nothing to do with the way it is stored in the database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ept View Post

    Both the server and the client have EST timezone with a checked checkbox for "automatically adjust clock for daylight saving changes".
    Still confused on why the timestamp values are not displayed as they are stored in the database in certain cases...
    Since in both cases where the time shows incorrectly you use Java programs (Aqua Data Studio and DB2 Command Editor), my guess would be that your Java locale settings are incorrect or the JDK installation does not have the DST fixes applied. What is the DB2 client version?

  8. #8
    Join Date
    Oct 2009
    Posts
    6
    Marcus_A, I meant that the displayed/exported timestamp values are off by one hour for the time periods when the daylight saving change takes into effect as indicated in the examples from my previous post.

    n_i, thanks for the hints. I'll try to check settings for Java locale and/or find DST fixes.

    DB2 client version as taken from CONTROL CENTER
    ================================================== ==========
    About DB2 Administration Tools Environment
    ================================================== ==========
    DB2 administration tools level:
    Product identifier SQL09070
    Level identifier 08010107
    Level DB2 v9.7.0.441
    Build level s090521
    PTF NT3297
    ================================================== ==========
    Java development kit (JDK):
    Level IBM Corporation 1.6.0
    ================================================== ==========

    AQUA STUDIO version is 8.0.13

Posting Permissions

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