Forum Stats

  • 3,768,176 Users
  • 2,252,755 Discussions
  • 7,874,483 Comments

Discussions

Categories

Converting date and time stored in UTC to local time

HawkerHunter
HawkerHunter Member Posts: 497 Bronze Badge
in APEX Discussions

Hello friends,

I am using Oracle APEX version 21.1 in OCI where I am capturing SYSTIMESTAMP to save any transaction in my APEX application which gets saved in UTC.

While displaying the date and time, I want to display the local time say of Sydney/Australia.

I can use the following to get the local time:

SELECT SYSTIMESTAMP AT TIME ZONE 'Australia/Sydney' FROM   DUAL;

But to know the list of available Time Zones in Oracle DB, I used the following query:

SELECT * FROM gv$timezone_names;

It returned TZNAME, TZABBREV and CON_ID. It seems the TZABBREV is for daylight saving hours.

Can anyone kindly let me know how to use both TZNAME, TZABBREV values in the above query to get the correct local time from UTC.

Thanks and Regards

Hawker

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    Accepted Answer

    when I insert or update a record, I use a row level trigger which puts the SYSTIMESTAMP into the CREATED_ON and UPDATED_ON field which are of TIMESTAMP data type.

    And then you are then converting CREATED_ON & UPDATED_ON from UTC to client time zone. TIMESTAMP data type has no time zone, so you are assuming UTC but in general SYSTIMESTAMP returns timestamp in database server time zone. Therefore you are relying on database time zone set to UTC. If you want to be bullet-proof then change your trigger to assign CREATED_ON & UPDATED_ON SYSTIMESTAMP AT TIME ZONE 'UTC' rather than just SYSTIMESTAMP. Or change CREATED_ON & UPDATED_ON datatype to TIMESTAMP WITH TIME ZONE and use just SYSTIMESTAMP. Then CREATED_ON & UPDATED_ON will hold database server time zone regardless of what it is set to and you will be able to convert it to local time zone.

    SY.

    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond

    You don't need to supply time zone for that. Just cast it:

    SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-04:00

SQL> select cast(timestamp '2021-09-18 17:00:00 utc' as timestamp with local time zone) from dual;

CAST(TIMESTAMP'2021-09-1817:00:00UTC'ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
18-SEP-21 01.00.00.000000 PM

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 497 Bronze Badge

    Hi Solomon,

    thanks for your reply. Actually I am using Oracle APEX in the cloud. So the local time zone happens to be UTC itself. so, the above query shall return the same date and time.

    I am saving all my transactions in UTC and want to display the time based on user selected time zone. Suppose, there are some users in Sydney, then for them I would like to convert the UTC with 'Australia/Sydney' time zone.

    Regards

    Hawker

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    edited Sep 19, 2021 11:02AM

    Modify login page. Get client browser time zone and then set session time zone to client browser time zone using APEX_UTILS.SET_SESSION_TIME_ZONE.

    SY.

    HawkerHunter
    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,125 Gold Crown

    You may also wish to consider the Application -> Globalisation Attributes -> Automatic Time Zone property

    image.png


    HawkerHunterpauljohny
    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 497 Bronze Badge

    Hi Solomon and Scott,

    Thanks for your valuable inputs.

    Just a few clarifications on the Global parameter "Automatic Time Zone".

    1) If we put this parameter on, will the transactions be saved in local time zone or in UTC.

    2) If it saves in UTC, and in a report I display the transaction date and time using the saved UTC, will it automatically convert the UTC to appropriate time zone and display?

    Thanks and Regards

    Hawker

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    edited Sep 21, 2021 11:32AM

    It has no affect on anything but displaying TIMESTAMP WITH LOCAL TIME ZONE datatype data and function CURRENT_TIMESTAMP result since it sets client session time zone. So when you cast timestamp to TIMESTAMP WITH LOCAL TIME ZONE oracle simply converts timestamp from its time zone (explicit if timestamp data type is TIMESTAMP WITH TIME ZONE or implicit if timestamp data type is TIMESTAMP) to session time zone. BTW, I just noticed "I am using Oracle APEX version 21.1 in OCI where I am capturing SYSTIMESTAMP to save any transaction in my APEX application which gets saved in UTC". SYSTIMESTAMP doesn't return timestamp in UTC. It returns timestamp in database server time zone. Most cloud databases set/recommend database server time zone to UTC but you can't just rely on it. I'd use systimestamp at time zone 'utc' when saving.

    SY.

    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 497 Bronze Badge

    Hi Solomon,

    when I insert or update a record, I use a row level trigger which puts the SYSTIMESTAMP into the CREATED_ON and UPDATED_ON field which are of TIMESTAMP data type. When I query the record, I find the time to be 10 hours behind Sydney time, so I assume that the TIMESTAMP of the cloud server is at UTC.

    Under Globalization Attributes, I had put the "Automatic Time Zone" on and used the following select query in my Oracle APEX report:

    select cast(a.CREATION_DATE as timestamp with local time zone) creation_date from EMP_MASTER;

    But it is not displaying the time as per the local time i.e.: it is not converting the UTC time of the saved record to Australia/Sydney time.

    It will be of great help if you can point out where I have made the mistake.

    Thanks & Regards

    Hawker

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    Accepted Answer

    when I insert or update a record, I use a row level trigger which puts the SYSTIMESTAMP into the CREATED_ON and UPDATED_ON field which are of TIMESTAMP data type.

    And then you are then converting CREATED_ON & UPDATED_ON from UTC to client time zone. TIMESTAMP data type has no time zone, so you are assuming UTC but in general SYSTIMESTAMP returns timestamp in database server time zone. Therefore you are relying on database time zone set to UTC. If you want to be bullet-proof then change your trigger to assign CREATED_ON & UPDATED_ON SYSTIMESTAMP AT TIME ZONE 'UTC' rather than just SYSTIMESTAMP. Or change CREATED_ON & UPDATED_ON datatype to TIMESTAMP WITH TIME ZONE and use just SYSTIMESTAMP. Then CREATED_ON & UPDATED_ON will hold database server time zone regardless of what it is set to and you will be able to convert it to local time zone.

    SY.

    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 497 Bronze Badge

    Hi Solomon,

    it has finally worked as I wanted.

    I changed the data type of the CREATED_ON and UPDATED_ON fields to "TIMESTAMP WITH TIME ZONE" and in the Trigger I am inserting "SYSTIMESTAMP AT TIME ZONE 'UTC'" to make sure it always saves the date time in UTC.

    In the front end APEX query I have used : cast(CREATION_DATE as timestamp with local time zone) and cast(UPDATED_ON as timestamp with local time zone).

    Also, in Globalization under shared components I have put on the "Automatic Time Zone" option.

    Thank you for your help.

    Regards

    Hawker

    image.png


    · Share on TwitterShare on Facebook