Results 1 to 6 of 6
  1. #1
    Join Date
    May 2017
    Posts
    3

    Unanswered: DB2 : Converting Date and Time From Eastern to UTC which are in separate columns

    Hi All,

    I am new to DB2. I have below requirements-
    --> In DB2 database I have table "MyTable1" with below columns.
    Efective_Date (Type: Date)
    Effective_Time (Type: Time)

    Currently value in this fields are in Eastern Time zone. I would need to convert it to UTC timezone.

    Can anyone please suggest me how can i achieve it? There are many post about UTC time conversion but in the scenario I have it instead of having signle column that has date and time value, I have 2 different columns that stores this. So none of the direct solution applies to me.

    Thanks in Advance.

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    simple google search returns all kinds of answers, including to asked/answered threads on this forum., Then its a simple date(of time_field) and time(of_time_field)

  3. #3
    Join Date
    May 2017
    Posts
    3
    Quote Originally Posted by DNance View Post
    simple google search returns all kinds of answers, including to asked/answered threads on this forum., Then its a simple date(of time_field) and time(of_time_field)
    Thanks DNance.

    I could just now able to achieve it with below query:

    select date(effective_timestamp - CURRENT_TIMEZONE), time(effective_timestamp - CURRENT_TIMEZONE) from(
    select timestamp(Effective_Date, Effective_Time) as effective_timestamp from (
    select Effective_Date,
    Effective_Time,
    from
    MyTable1
    )) ;

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Just in case. You should know that this works if your current time zone is equal to Eastern only.
    Regards,
    Mark.

  5. #5
    Join Date
    May 2017
    Posts
    3
    Quote Originally Posted by mark.bb View Post
    Just in case. You should know that this works if your current time zone is equal to Eastern only.
    That is correct Mark. The server on which this query will run (as well database) is in EST timezone.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    You have a flaw in the logic. You are using the CURRENT TIMEZONE to convert to UTC. This will be accurate for only EFFECTIVE_DATE/EFFECTIVE_TIME that match whether they and CURRENT TIMEZONE are in or out of Daylight savings time. If you use this algorithm to convert when the date is in January (Standard time) while the computer is in Daylight Saving time (like today), your result will be incorrect.

    Andy

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
  •