Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: DB2 Timestamp with timezone

    Hello,

    Any idea how I can get a timestamp passing in a timezone parameter? For example, I have a stored procedure that uses pacific time at midnight for the current day.

    How can I use the timestamp function to give me the correct time based on PDT/PST?

    PST -- it should give 2008-05-16-08.00.00.000000

    PDT -- it should give 2008-05-16-07.00.00.000000

    currently I'm doing this which is wrong... since it only sets the time to 07:00 every day of the year...
    SET currentDate = TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME('07.00.00'));


    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure I understood your question but look at CURRENT_TIMEZONE.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    thanks for the response. I just want to set the timezone to US/Pacific in my stored procedure and then then get the correct midnight timestamp for the day based on whether it's daylight savings time or not. Currently, I'm passing the string time '07:00:00' which I don't want to ... I want the sproc to figure out the correct GMT time without me hard cording it. hope this makes more sense..thx!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    db2 => with t (local_time, utc_time) as (
    db2 (cont.) => values (current_timestamp, current_timestamp - current timezone)
    db2 (cont.) => ) select * from t
    db2 (cont.) => ;
    
    LOCAL_TIME                 UTC_TIME
    -------------------------- --------------------------
    2008-05-16-16.09.52.430000 2008-05-16-20.09.52.430000
    
      1 record(s) selected.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    thanks... this is what i get...

    db2 "with t (local_time, utc_time) as ( values (current_timestamp, current_timestamp - current timezone) ) select * from t"

    LOCAL_TIME UTC_TIME
    -------------------------- --------------------------
    2008-05-16-20.32.57.403224 2008-05-16-20.32.57.403224

    1 record(s) selected.


    our db must be in utc timezone then... is there some way to set the timezone to pacific for the session only in my sproc? thanks for all the help!

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Subtracting CURRENT TIMEZONE from a local time converts that local time to UTC. The time is calculated from the operating system time at the moment the SQL statement is executed. (The CURRENT TIMEZONE value is determined from C runtime functions.)
    CURRENT TIMESTAMP - CURRENT TIMEZONE

    Never mind. Forgot to refresh after the meeting
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user
    is there some way to set the timezone to pacific for the session only in my sproc?
    No, because DB2 obtains its timezone information from the operating system, and not from the session environment.

    Normally all time manipulations should be performed at the client side of things, because that's where formats and timezone offsets matter. You could pass the current timezone offset from the client to the stored procedure, as an example.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    great..thanks for the reply! that helps a lot...

Posting Permissions

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