Hi
Here in the UK we only have one time zone and in the majority of cases applications are only used in the UK. We are lucky that we don't have to handle users in different time zones, but we do need to handle daylight savings.
I am working on an application that could be used by users around the world and have been trying to work out how to handle dates and times. Everyone would want to see the time in their local time, but I can't seem to get anything that does this. I've done a lot of reading on this, including general pages on Stackoverflow that discuss saving all times in UTC and then converting to the users local time at the last minute. This seems sensible. If the database time is UTC and all times are stored UTC, then not only does DST not apply, but any automated processing wouldn't not need to convert the time (e.g. if someone asked for an email to be automatically sent at a certain time I don't need to know what time zone they are in as everything is UTC). I hope I'm making sense.
Anyway, I tried to use TIMESTAMP WITH LOCAL TIME ZONE in APEX. This sounds great, times are stored in UTC (database zone) but shown in the users local time. The users time zone is automatically set by APEX. BUT this doesn't seem to work. The UK is currently in GMT (+0) and I update a record in my table through SQL*Developer using the following (this is just an example, not real application code!):
update meetings_timestamp_tzl set modified_date = to_timestamp('01-SEP-2014 12:15','DD-MON-YYYY HH24:MI');
Modified_date is a TIMESTAMP WITH LOCAL TIME ZONE column and when I query the table through SQL*Plus I get the following:
SQL> select DBTIMEZONE from dual;
DBTIME
------
+00:00
SQL> select * from dev.meetings_timestamp_tzl;
ID
----------
NAME
----------------------------------------------
MEETING_DATE
----------------------------------------------
MODIFIED_DATE
----------------------------------------------
2
Test
01-SEP-14 11.15.00.000
This shows the time as 11:15 which is UTC. On 01-SEP-2014 the UK is in BST (+1) so showing as 11:15 is correct for UTC. However, when I display the field through APEX it also shows the time as 11:15, which is incorrect as on this day it will be 12:15. Now I did a little experiement and set my PC date through Windows to be 1st May 2015 which is in BST. I then logged into APEX and the same time was shown as 12:15.
From this I can only assume that APEX is looking at my current time zone and NOT my locale. So DST will be applied to all values depending on whether or not we are currrently in DST. My research has found other forum posts that show that when using LOCAL or a specific time zone in Oracle DST is always ignored or always applied. Is my understanding correct?
I have also tried using TIMESTAMP WITH TIME ZONE instead. Using the built-in APEX date picker to select a date and time this is stored as 01-SEP-2014 12:15 +00:00. But this is incorrect because on 1st September we are in +01:00.
I have found some references to people who have dates stored in UTC that want to show them in a different time zone which takes into account DST. For these a possible solution is (assuming your column is a DATE):
CAST((FROM_TZ(CAST(date_column AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)
This would show a date saved as UTC in 'US/Eastern' time taking into account DST dates. But for this to work you have to know the users locale (TZR), NOT the time zone and this isn't available through the browser.
So my questions are:
1 - Is there any way to store a date in UTC (or any time zone) and display it back in the users local time taking into account DST without knowing the users locale?
2 - How do other people handle date/times when users are in mutliple time zones that may or may not observe DST? We have two possible solutions, the user has to store their locale in the database or all times are shown in a certain time zone with a big disclaimer! How do developers in countries that have multiple time zones (e.g. America or Australia) handle this?
3 - Is the TIMESTAMP WITH LOCAL TIME ZONE a bug in APEX? I ask because on 29th March when the clocks go forward all values that are stored in columns of this type will change by an hour, then in October they will change back again. So the times shown through APEX change throughout the year and you don't know what the actual time is. I'm not sure what the purpose is for the automatic time zone, unless you are only using automatically created dates using current_date or current_timestamp otherwise you run into DST problems.
Many thanks for your time and sorry the post is so long but it is a complex and I think important area. Also, apologies if there is a great forum post on this but I did do lots of searches and couldn't find anything.
Sara