Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Unanswered: retrieve timezone from timestamp value

    Is there any way to retrieve the time zone value from timestamp value in db2.For eg: if i give the timestamp '2013-04-25-21.12.30.000000' as input in query , i need to get the corresponding timezone for timestamp as output .Please let me know way to do this

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    There is a 'current timezone' special registry.
    If your given timestamp is in UTC then to get corresponding local time you could use the following expression:
    Code:
    timestamp('2013-04-25-21.12.30.000000') + current timezone
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    Quote Originally Posted by mark.b View Post
    There is a 'current timezone' special registry.
    If your given timestamp is in UTC then to get corresponding local time you could use the following expression:
    Code:
    timestamp('2013-04-25-21.12.30.000000') + current timezone

    Thanks for reply. If the timestamp is not in UTC is there any way to retrieve.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by udnapp View Post
    If the timestamp is not in UTC is there any way to retrieve.
    There is no timezone information in timestamps.
    If your timestamp is in a local timezone, you can get the corresponding UTC time by this:
    Code:
    timestamp('2013-04-25-21.12.30.000000') - current timezone
    If this is still not you want to get, then please, provide us your understanding of "retrieving timezone from a timestamp" with some example: what you have and what you want to get...
    Regards,
    Mark.

  5. #5
    Join Date
    Sep 2013
    Posts
    3
    Quote Originally Posted by mark.b View Post
    There is no timezone information in timestamps.
    If your timestamp is in a local timezone, you can get the corresponding UTC time by this:
    Code:
    timestamp('2013-04-25-21.12.30.000000') - current timezone
    If this is still not you want to get, then please, provide us your understanding of "retrieving timezone from a timestamp" with some example: what you have and what you want to get...
    Our problem is when i give select TIMESTAMP('2004-06-30T00:00:00') - CURRENT TIMEZONE from
    sysibm.sysdummy1 as input i am getting the out put as 2004-06-30-04.00.00
    where as when i give select TIMESTAMP('2005-03-18T00:00:00') - CURRENT TIMEZONE from
    sysibm.sysdummy1 the output is 2005-03-18-05.00.00. Is the time zone dependent on some column?How can we know timezone for a column in that case?

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What are your db2 version and platform?
    This is wrong timestamp constant - '2004-06-30T00:00:00'.
    At least on DB2 for Linux, Unix and Windows...
    Regards,
    Mark.

Posting Permissions

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