Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    280

    Unanswered: GENERATED ALWAYS FOR EACH ROW ON UPDATE in UTC?

    I'm slowly catching up with what's new in 9.7. Is there anyway to specify that a column declared as:

    EMP_INFO_CHANGED_TIME TIMESTAMP NOT NULL
    GENERATED ALWAYS FOR EACH ROW ON UPDATE
    AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN

    should use UTC instead of local time (other than changing server time). If possible, I would like to avoid DST issues.

    /Lennart

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the CURRENT TIMEZONE special register. I don't know for sure, but I would assume that DB2 stores timestamps in UTC internally. When querying, DB2 uses per default the local timezone. If you want to get the data in UTC, the query should be written as such like:
    Code:
    SELECT emp_info_changed_time - CURRENT TIMEZONE
    FROM ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze View Post
    I would assume that DB2 stores timestamps in UTC internally.
    I don't think it's the case; it's up to the client application to interpret what's inserted or retrieved.

    @lelle12, please note that CURRENT TIMEZONE refers to the operating system setting on the server, not the client (if the latter is in a different timezone).

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    What I would like to do is store the ts i UTC. First thought was to use another generated column based on

    EMP_INFO_CHANGED_TIME - current_timezone

    But that is not allowed (SQL0548N). I suspect that is due to the check constraint that is created, since an explicit check constraint gives the same error. Finally it is not allowed to reference a generated column in a before trigger ( SQL20094N ).

    Are there any rumours on timestamp with timezone in coming releases?


    /Lennart

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i View Post
    I don't think it's the case.
    You think so? I can't test this myself. That would mean a reconfiguration of the timezone in the server's operating system would not change the external representation of timestamp values returned by a simple query. If the values do change, then DB2 uses UTC (or what it believes to be UTC) internally. As I said, I don't know what DB2 is doing, but it is easy to test that way.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

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
  •