Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

Timestamp with time zone

Dragica Smintic

Dragica SminticMon March 13, 2023 09:29 AM

Try with SELECT TIMESTAMP_TZ(TIMESTAMP('2005-10-10') ) -- ...

  • 1.  Timestamp with time zone

    Posted Fri March 10, 2023 11:44 AM

    Good afternoon -

    I need to convert a date; 2005-10-10, into a timestamp with time zone.

    Example: 2005-10-10T00:00:00.000-04:00

     

    There's a DB2 function TO_UTC_TIMESTAMP; however, that function isn't available in z/os.  

    Does anyone know of a way to do this in Db2 for z/OS?   

     

     

     

     



  • 2.  RE: Timestamp with time zone

    IBM Champion
    Posted Mon March 13, 2023 03:42 AM

    Hi!

     

    This works for me:

     

    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------

    select date('2005-10-10')                                               00000172                         

     , timestamp('2005-10-10')                                              00000273                         

     , timestamp('2005-10-10') at local                                     00000374                         

     , timestamp('2005-10-10') at timezone '-04:00'                         00000476                         

    from sysibm.sysdummy1 ;                                                 00000575                         

    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------

                                                                                                             

    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------

    2005-10-10  2005-10-10-00.00.00.000000  2005-10-10-00.00.00.000000+01:00  2005-10-09-19.00.00.000000-04:00

    DSNE610I NUMBER OF ROWS DISPLAYED IS 1                                                                   

     

    Hope that helps!

     

     

     

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

     

    ibm-champion-2023 email

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email:     R.Boxwell@seg.de
    Web  http://www.seg.de

    Link zur Datenschutzerklärung


    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     




    Original Message


  • 3.  RE: Timestamp with time zone

    Posted Tue March 14, 2023 12:49 PM

    This explains it – thanks everyone !

     

    IBM's response:
    Db2 has no concept of daylight savings time versus non-DST. Db2 also has no concept of the cutover between those dates. All Db2 can do is use the z/OS system and hardware to determine GMT and local time.

    So, The behavior that you're seeing is expected.

     




    Original Message


  • 4.  RE: Timestamp with time zone

    Posted Mon March 13, 2023 09:29 AM

    Try with

    SELECT                                    
        TIMESTAMP_TZ(TIMESTAMP('2005-10-10') ) -- or CAST(TIMESTAMP('2005-10-10') AS TIMESTAMP WITH TIME ZONE)
    FROM SYSIBM.SYSDUMMY1;    

    result: 2005-10-10-00.00.00.000000+01:00               

     

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-timestamp-tz



    ------------------------------
    Dragica Smintic
    ------------------------------

    Original Message


  • 5.  RE: Timestamp with time zone

    Posted Mon March 13, 2023 09:30 AM

    Maybe try something like below:

    SELECT TIMESTAMP_TZ(TIMESTAMP_FORMAT('2005-10-10', 'YYYY-MM-DD'), '-04:00')                                        

    FROM SYSIBM.SYSDUMMY1;                                            

                                                                                                                                                                                      

        +-----------------------------------------------------------------

        |                                                                 

        +-----------------------------------------------------------------

      1_| 2005-10-10-00.00.00.000000-04:00                                

        +----------------------------------------------------------------- 



    ------------------------------
    YING ZHANG
    ------------------------------

    Original Message


  • 6.  RE: Timestamp with time zone

    IBM Champion
    Posted Mon March 13, 2023 09:30 AM

    Hi Donna,

    I think that the function you are looking for is TIMESTAMP_TZ

    Regards



    ------------------------------
    Ivan Losada
    ------------------------------

    Original Message


  • 7.  RE: Timestamp with time zone

    Posted Mon March 13, 2023 09:30 AM

    Good day Donna,

    at zOS we have the CURRENT TIME ZONE, for example I am 1 hour ahead from UTC, so, if I ask for the CURRENT TIME ZONE, it returns 010000, being the first to digit the difference in hours: (CURRENT TIME ZONE - IBM Documentation)

    select current time zone from sysibm.sysdummy1; 
    ---------+---------+---------+---------+--------

      10000,                                        

    If I want to know the current time in UTC, I would query this:

    select current timestamp - current time zone from sysibm.sysdummy1; 
    ---------+---------+---------+---------+---------+---------+--------

    2023-03-13-07.34.23.186439                                          

    So with this CURRENT TIME ZONE you can convert it to the "-04:00" of your Example.

    Hope that helps!



    ------------------------------
    Soledad Martinez
    ------------------------------

    Original Message


  • 8.  RE: Timestamp with time zone

    Posted Mon March 13, 2023 06:34 PM

    Thanks for the responses.

    Here in Ontario we are currently in DST daylight savings time which should be an offset of -4.

    But I don't want Db2 to use our current DST to determine the offset – I want Db2 to use my input date.

    Notice in the example below – when input date is July DST – I get an offset of -4 which is correct.

    However when input date is January (not DST) – the offset should be -5 however it's still -4.... which I believe is incorrect.  

     

    SELECT,TIMESTAMP_TZ('2023-01-01-00.00.00.000000'),

      FROM SYSIBM.SYSDUMMY1;

    ---------+---------+---------+---------+---------+---------+--

     

    ---------+---------+---------+---------+---------+---------+--

    2023-01-01-00.00.00.000000-04:00

    DSNE610I NUMBER OF ROWS DISPLAYED IS 1

    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

    ---------+---------+---------+---------+---------+---------+--

    SELECT TIMESTAMP_TZ('2023-07-01-00.00.00.000000')

      FROM SYSIBM.SYSDUMMY1;

    ---------+---------+---------+---------+---------+---------+--

     

    ---------+---------+---------+---------+---------+---------+--

    2023-07-01-00.00.00.000000-04:00

     

     




    Original Message


  • 9.  RE: Timestamp with time zone

    IBM Champion
    Posted Fri June 23, 2023 05:14 PM

    you can use the CURRENT TIMEZONE function to retrieve the current time zone offset and concatenate it with the timestamp.

    

    SELECT TIMESTAMP('2005-10-10' || ' 00:00:00.000') || CURRENT TIMEZONE AS timestamp_with_timezone
FROM SYSIBM.SYSDUMMY1;

    Hope this helps !



    ------------------------------
    Youssef Sbai Idrissi
    Software Engineer
    ------------------------------

    Original Message


  • 10.  RE: Timestamp with time zone

    Posted Mon November 20, 2023 10:38 AM

    Certainly! In Db2 for z/OS, you can convert a date '2005-10-10' into a timestamp with time zone using this query:

    ```sql
    SELECT TIMESTAMP_FORMAT(TIMESTAMP(TO_DATE('2005-10-10', 'YYYY-MM-DD'), '00:00:00.000'), 'YYYY-MM-DDTHH24:MI:SS.FFF-04:00') FROM SYSIBM.SYSDUMMY1;
    ```

    This query combines date and time, then formats it with the desired time zone offset (-04:00). Adjust the format string as needed.



    ------------------------------
    Idrees khan
    ------------------------------

    Original Message


  • 11.  RE: Timestamp with time zone

    Posted Tue November 21, 2023 05:56 PM

    Idees,

     

    That doesn't work for me in Data Studio.  What I get is:

     

    (CODE)

    SELECT TIMESTAMP_FORMAT(TIMESTAMP(TO_DATE('2005-10-10', 'YYYY-MM-DD'), '00:00:00.000'), 'YYYY-MM-DDTHH24:MI:SS.FFF-04:00') FROM SYSIBM.SYSDUMMY1;

    (MESSAGE)

    THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF TIMESTAMP IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.72.44

     

    ****

    Is there something obvious which I'm doing wrong? I cut-and-pasted the SQL from your doc, but of course I could have fat-fingered something.

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 (m)

     

     




    Original Message


  • 12.  RE: Timestamp with time zone

    IBM Champion
    Posted Wed November 22, 2023 02:24 AM

    Hi!

    This works for me:

    -- RETURNS 2005-10-3110-00.00.00.000                                    
    SELECT                                                                  
    TIMESTAMP(TO_DATE('2005-10-31', 'YYYY-MM-DD'), 3)                       
    FROM SYSIBM.SYSDUMMY1                                                   
    ;                                                                       
    SELECT                                                                  
    TIMESTAMP_TZ(TIMESTAMP(TO_DATE('2005-10-31', 'YYYY-MM-DD'), 3),'-04:00')
    FROM SYSIBM.SYSDUMMY1                                                   
    ;                                                                       



    ------------------------------
    Roy Boxwell
    Senior Software Architect
    Software Engineering GmbH
    Duesseldorf
    +4921196149675
    ------------------------------

    Original Message


  • 13.  RE: Timestamp with time zone

    Posted Wed November 22, 2023 02:40 AM

    For the record, the problem with TIMESTAMP(TO_DATE('2005-10-10', 'YYYY-MM-DD'), '00:00:00.000') is that the time portion should either have dots, not colons, as separators or be in USA format with no seconds portion and an AM or PM signifier.  As others have used: '2005-10-10' is a standard date format - so does not need the TO_DATE function; and the time portion defaults to zero - so "00.00.00.000" does not need to be specified.



    ------------------------------
    James Campbell
    ------------------------------

    Original Message


  • 14.  RE: Timestamp with time zone

    Posted Thu November 23, 2023 10:33 AM

    Hello Donna,

    for me it seems that you're looking for this special kind of the translate-function, that rearranges the digits of the date:

    SELECT TRANSLATE('ABCDEFGHIJT00:00:00.000-4:00',
                     '2005-10-10','ABCDEFGHIJ')     
    FROM SYSIBM.SYSDUMMY1;                          

     +------------------------------+
    !                              !
    +------------------------------+
    ! 2005-10-10T00:00:00.000-4:00 !
    +------------------------------+

    But you have to code the timezone-value by yourself.

    Kind regards, Rolf



    ------------------------------
    Rolf Drees
    ------------------------------

    Original Message


  • 15.  RE: Timestamp with time zone

    Posted Mon November 27, 2023 03:16 PM

    Hello Donna,

    Here are two options . 

    1)  Cast the date in to timestamp and cast the timestamp again into timestamp with timezone. 

    select cast(cast(date('2005-10-10')  as timestamp)  as timestamp with timezone)                                  
    from sysibm.sysdummy1;                                       

    2005-10-10-00.00.00.000000-05:00                            

    2) case the date in time stamp and use the TIMESTAMP_TZ function to add the time zone to the timestamp. 

    select timestamp_tz(timestamp('2005-10-10')) from sysibm.sysdummy1;                                
    2005-10-10-00.00.00.000000-05:00                                        

    Prasad

                                 



    ------------------------------
    Prasad Rao Mocharla
    ------------------------------

    Original Message


  • 16.  RE: Timestamp with time zone

    Posted Thu November 23, 2023 10:35 AM

    Hello Donna,

    for me this sounds like that you're looking for this special kind of the translate-function, that rearranges the digits:

    SELECT TRANSLATE('ABCDEFGHIJT00:00:00.000-4:00',
                     '2005-10-10','ABCDEFGHIJ')     
    FROM SYSIBM.SYSDUMMY1;                          

    +------------------------------+
    !                              !
    +------------------------------+
    ! 2005-10-10T00:00:00.000-4:00 !
    +------------------------------+

    But you have to code the timezone by yourself.

    Kind regards, Rolf



    ------------------------------
    Rolf Drees
    ------------------------------

    Original Message