Forum Stats

  • 3,815,220 Users
  • 2,258,979 Discussions
  • 7,893,004 Comments

Discussions

Entering date from different time zones in apex

SACH!N-Oracle
SACH!N-Oracle Member Posts: 179 Employee
edited Mar 12, 2018 9:26AM in APEX Discussions

Hi,

I have to develop an application that should support multiple time zones.

In short, a user in any timezone should be able to create a record with a date in the parent timezone (let the parent timezone be UTC).

Also the users should be able to view the records in their time-zones.

What is the best way for achieving this?

Please help/guide.

Please let me know if more information is required.

Thanks,

Sachin

Tagged:
SACH!N-Oracle

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,806 Red Diamond
    edited Mar 12, 2018 9:26AM Answer ✓
    SACH!N-Oracle wrote:i went through the documents you had suggested.I set the "Automatic Time Zone" property in the application_properties to "YES".Then i tried rendering a simple report which select SYSDATE with TIME from dual:
    1. selectto_char(sysdate,'MM/DD/YYYYHH24:MI:SSA.M.')as"Date&Time"fromdual;
    But the report still shows the time in UTC.

    As stated in one of those documents:

    All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

    sysdate returns a date.

    SACH!N-OracleSACH!N-Oracle

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,180 Silver Crown
    edited Mar 9, 2018 9:37AM

    So, you want all DATE values in the database to be in UTC timezone, but you want the end-user to Enter/View data in their Local Time Zone.

    Right?

    MK

    SACH!N-Oracle
  • SACH!N-Oracle
    SACH!N-Oracle Member Posts: 179 Employee
    edited Mar 12, 2018 3:12AM

    @Mike Kutz,

    Yes Mike, that's exactly my requirement.

    Thanks,

    Sachin

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,806 Red Diamond
    edited Mar 12, 2018 4:33AM
    SACH!N-Oracle wrote:I have to develop an application that should support multiple time zones.In short, a user in any timezone should be able to create a record with a date in the parent timezone (let the parent timezone be UTC).Also the users should be able to view the records in their time-zones.What is the best way for achieving this?

    This should be enough to get things started:

    However, noting that you are Oracle employee, you should check the database time zone in your proposed environment(s) if you intend to run the application on apex.oraclecorp.com or other hosted/cloud provision. I see that the database time zone on apex.oracle.com is -05:00 not UTC, which runs counter to the recommendations for using TIMESTAMP WITH LOCAL TIME ZONE. Using a non-zero offset may prove counterintuitive and cause data portability issues.

    SACH!N-OracleSACH!N-Oracle
  • SACH!N-Oracle
    SACH!N-Oracle Member Posts: 179 Employee
    edited Mar 12, 2018 5:27AM

    Thanks @fac586.

    Let me check & try this out.

    Thanks,
    Sachin

  • SACH!N-Oracle
    SACH!N-Oracle Member Posts: 179 Employee
    edited Mar 12, 2018 9:04AM

    Hi @fac586,

    i went through the documents you had suggested.

    I set the "Automatic Time Zone" property in the application_properties to "YES".

    Then i tried rendering a simple report which select SYSDATE with TIME from dual:

    select to_char (sysdate, 'MM/DD/YYYY HH24:MI:SS A.M.') as "Date & Time" from dual;

    But the report still shows the time in UTC.

    I have created a sample workspace for you to check:

    app url: https://apex.oracle.com/pls/apex/f?p=144695:2:117850758091860:::::

    workspace: SACHIN_TEST

    user: test

    pass: test

    Please help/guide.

    Thanks,
    Sachin

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,806 Red Diamond
    edited Mar 12, 2018 9:26AM Answer ✓
    SACH!N-Oracle wrote:i went through the documents you had suggested.I set the "Automatic Time Zone" property in the application_properties to "YES".Then i tried rendering a simple report which select SYSDATE with TIME from dual:
    1. selectto_char(sysdate,'MM/DD/YYYYHH24:MI:SSA.M.')as"Date&Time"fromdual;
    But the report still shows the time in UTC.

    As stated in one of those documents:

    All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

    sysdate returns a date.

    SACH!N-OracleSACH!N-Oracle
This discussion has been closed.