Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    22

    Angry Unanswered: Oracle Date Datatype - Formatting date from US to Japan local time.

    Hi,

    Datatype: Date
    DB Server / UNIX server location: US timezone: CST
    Query is initiated from client in Japan say "select x,y, sysdate .. from a,b;"

    When the query returns results, the date data comes in US local time. I cannot change the db instance time or the UNIX server system time to Japan time. How can I format the sysdate (date datatype) to get the JAPAN time.

    NOTE that when the US timezone changes, this should not affect the function.

    Any help appreciated.


    Regards,
    Aravind

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    AFAIK, NLS parameters are not sufficient here, and you need a little trick.

    What you should do is : sysdate - (server timezone offset) + (client timezone offset).

    Code:
    select
    to_char
    (
       Sysdate
       , 'DD/MM/YYYY:HH24:MI:SS'
    ) Local_Datetime,
    to_char
    (
       Sysdate 
       - (to_number(to_char(systimestamp,'TZH'))/24 + to_number(to_char(systimestamp,'TZM'))/1440)
       , 'DD/MM/YYYY:HH24:MI:SS'
    ) UTC_Datetime,
    to_char
    (
    Sysdate 
       - (to_number(to_char(systimestamp,'TZH'))/24 + to_number(to_char(systimestamp,'TZM'))/1440)
       + (to_number(substr(tz_offset('Asia/Tokyo'),1,3))/24 + to_number(substr(tz_offset('Asia/Tokyo'),5,2))/1440)
       , 'DD/MM/YYYY:HH24:MI:SS'
    ) Japan_Datetime
    from dual;
    Code:
    rbaraer@Ora10g> select
      2  to_char
      3  (
      4     Sysdate
      5     , 'DD/MM/YYYY:HH24:MI:SS'
      6  ) Local_Datetime,
      7  to_char
      8  (
      9     Sysdate
     10     - (to_number(to_char(systimestamp,'TZH'))/24 + to_number(to_char(systimestamp,'TZM'))/1440)
     11     , 'DD/MM/YYYY:HH24:MI:SS'
     12  ) UTC_Datetime,
     13  to_char
     14  (
     15  Sysdate
     16     - (to_number(to_char(systimestamp,'TZH'))/24 + to_number(to_char(systimestamp,'TZM'))/1440)
     17     + (to_number(substr(tz_offset('Asia/Tokyo'),1,3))/24 + to_number(substr(tz_offset('Asia/Tokyo'),5,2))/1440)
     18     , 'DD/MM/YYYY:HH24:MI:SS'
     19  ) Japan_Datetime
     20  from dual;
    
    LOCAL_DATETIME      UTC_DATETIME        JAPAN_DATETIME
    ------------------- ------------------- -------------------
    16/11/2005:09:39:12 16/11/2005:08:39:12 16/11/2005:17:39:12
    
    rbaraer@Ora10g>
    HTH & Regards,

    RBARAER

    PS : I had an almost similar need, and I created a function that returns the local date provided you give the local TZ name as argument (eg 'Asia/Tokyo'). If you store the tz name for each client, then you'll be able to calculate the local time for each of them with this function.
    Last edited by RBARAER; 11-16-05 at 04:52.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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