Entering date from different time zones in apex

SACH!N-Oracle

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

fac586

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.

Mike Kutz

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

@Mike Kutz,

Yes Mike, that's exactly my requirement.

Thanks,

Sachin

fac586

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:

• Automatic Time Zone support in Application Express 4.0
• https://pellepim.bitbucket.io/jstz/

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-Oracle

Thanks @fac586.

Let me check & try this out.

Thanks,
Sachin

SACH!N-Oracle

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

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.