Db2 for z/OS & Db2ZAI

 View Only

  • 1.  Timestamp with time zone

    Posted 15 days ago

    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 12 days ago

    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 11 days ago

    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 12 days ago

    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 12 days ago

    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 12 days ago

    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 12 days ago

    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 12 days ago

    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