Zoulfikar, March 20, 2023 - 8:58 am UTC
Thank you for your time
March 20, 2023 - 11:44 am UTC
You're welcome
Simpler workaround
mathguy, March 20, 2023 - 4:49 pm UTC
An even simpler workaround is to enclose the bind variable in parentheses:
SQL> variable tz varchar2(100)
SQL> exec :tz := 'UTC'
PL/SQL procedure successfully completed.
SQL> select systimestamp at time zone :tz as ts_at_utc from dual;
select systimestamp at time zone :tz as ts_at_utc from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select systimestamp at time zone (:tz) as ts_at_utc from dual;
TS_AT_UTC
---------------------------------------------------------------------------
20-MAR-23 04.39.18.371953 PM UTC
It is interesting to note where the parser thinks it found an error in the first query. It thinks the token TIME is out of place, it expected FROM there. Which means that the bind variable prevents it from viewing the full construct AT TIME ZONE ... and instead it goes back, it interprets AT as the column alias for SYSTIMESTAMP, and it expects that to be followed by FROM. This gives us just some vague, general insight into the nature of this bug.
March 21, 2023 - 5:29 pm UTC
Good investigation, thanks
timestamp time zone
zoulfikar, March 21, 2023 - 10:48 am UTC
I am currently working on an application where users from all around the world can book appointments.
What time zone should I use to store my dates? Should I use each user's time zone (which means I'll have a different time zone for each user), or should I use a neutral time zone like UTC? Currently, I store dates in the following format: 11-APR-23 11.30.00.000000 AM EUROPE/ROME. Do you have any advice regarding this issue?
the way I convert the date from the Page Item in Oracle Apex is to timestamp with time zone - time zone of the user:
L_START_DATE := from_tz(CAST (TO_TIMESTAMP_TZ( :P51010_START_DATE , 'YYYY-MM-DD"T"HH24:MI' ) AS TIMESTAMP), :GP_TZ);
where
L_START_DATE is the locale variable (timestamp with time zone);
P51010_START_DATE page item in the form where the user can choose the beginning of the appointment, with a format mask:YYYY-MM-DD"T"HH24:MI.
:GP_TZ is a global Item that takes automatically the time zone of the users
March 21, 2023 - 5:32 pm UTC
How are you determining the users' time zones?
As customers will want/need to know the time of the appointment in their local time zone, how will you know this if you store the values in UTC?
TIMESTAMP WITH LOCAL TIME ZONE
mathguy, March 21, 2023 - 8:14 pm UTC
Have you considered the data type TIMESTAMP WITH LOCAL TIME ZONE? From what you describe, it may be what you need.
Inputs to a column of this data type can be DATE, TIMESTAMP (both without time zone specified - they will be considered in the inputting user's local, or session, time zone) or TIMESTAMP WITH TIME ZONE (here the time zone may be any time zone). In all cases, the value is given a time zone (session time zone by default, if the value doesn't already have a time zone), then it is converted to the database time zone (which is often UTC, but it can be any time zone - it doesn't really matter), and only the resulting timestamp is stored. When the same column is queried, the value is converted to a timestamp with time zone (always the database time zone), and then further converted to the querying user's local, or session, time zone.
So if a user sets up a meeting for 10 AM tomorrow, London time, and a user from Rome looks it up, it will show as 11 AM his time (if I have the time difference right). Neither the inputting user nor the querying user will know anything about time zones, they will see everything as if only their own time zone existed. Nor does it matter what is actually stored on disk; in most cases it would be the equivalent time at UTC (which may or may not be the same as London time zone, due to DST).
March 22, 2023 - 2:32 pm UTC
Good suggestion
timestamp time zone
zoulfikar, March 22, 2023 - 7:58 am UTC
timestamp time zone
zoulfikar, March 22, 2023 - 8:24 am UTC
So, do you think using TIMESTAMP WITH LOCAL TIME ZONE solves the problem? Without any need to convert anything? Currently, I am using columns with the data type TIMESTAMP(6) WITH TIME ZONE, and when I select the local time zone, I get this date saved in the database: 22-MAR-23 12.00.00.000000 PM EUROPE/ROME. This data cannot be considered TIMESTAMP WITH LOCAL TIME ZONE. Nevertheless, I appreciate your answers. I am new to programming, and working with data is not easy for me at the moment. Could you recommend some websites with detailed explanations on this topic?
March 22, 2023 - 2:31 pm UTC
As mathguy says above, this stores the values in the database time zone. When you fetch them the value is in the time zone of the session. Change the time zone of the session the time shown will be converted to that time zone.
So this method works well - provided you can correctly determine which time zone the user is in.
WITH TIME ZONE vs. WITH LOCAL TIME ZONE
Sergiusz, March 23, 2023 - 2:42 am UTC
The data type TIMESTAMP WITH LOCAL TIME ZONE has the advantage of doing a conversion to/from the SESSIONTIMEZONE automatically on input and output. However, you need to make sure that SESSIONTIMEZONE is set properly to a user's preferred time zone during each page processing in APEX. I have not written anything in APEX for ages, so I do not remember whether you can make APEX do it for you automatically or whether you need to add some custom process.
On the other hand, TIMESTAMP WITH LOCAL TIME ZONE has one significant disadvantage for storage of future timestamps--it does not keep information about the original time zone in which a meeting organizer wants to define the meeting. This has paramount importance for cases where the DST rules change between the date a meeting is created and the date when it takes place. While most countries have stable DST rules and the risk of such change of rules is very low, there are countries, where the rules can be set each year, often shortly before the clock adjustment date. An upgrade of the DB time zone file takes care of adjusting stored TIMESTAMP WITH TIME ZONE values in affected time zones so that local time is preserved (internally stored UTC is adjusted). TIMESTAMP WITH LOCAL TIME ZONE values are not adjusted. They are stored in the local time of the DBTIMEZONE, so no adjustment is ever considered needed. Of course, TIMESTAMP WITH TIME ZONE values need to be converted manually on input and output from/to other preferred time zones with the AT TIME ZONE expression.
Note that the expression:
L_START_DATE := from_tz(CAST (TO_TIMESTAMP_TZ( :P51010_START_DATE , 'YYYY-MM-DD"T"HH24:MI' ) AS TIMESTAMP), :GP_TZ);
is incorrect. It should be:
L_START_DATE := from_tz(to_timestamp( :P51010_START_DATE , 'YYYY-MM-DD"T"HH24:MI' ), :GP_TZ);
March 23, 2023 - 1:36 pm UTC
Good points Sergiusz
timestamp time zone
zoulfikar fahs, March 23, 2023 - 1:05 pm UTC
Thank you all for your contributions. However, my decision to use TIMESTAMP WITH TIME ZONE is based on a comment I found on the web, which seems to be a shared opinion among developers. The comment states that
"TIMESTAMP WITH LOCAL TIME ZONE is not fundamental. It is just a convenience. We could live perfectly fine without it; wherever it is used (when it is used correctly, which isn't always the case), we could instead use TIMESTAMP WITH TIME ZONE, at the cost of more code in our queries. Let me say that a different way: You can always use TIMESTAMP WITH TIME ZONE to do absolutely everything you can with TIMESTAMP WITH LOCAL TIME ZONE, it's just a little more work. However, the opposite is absolutely not true; TIMESTAMP WITH LOCAL TIME ZONE has less information.".
"I think I will adopt the solution ot
TIMESTAMP WITH TIME ZONE because changing the table now will cost me a lot. However, I am interested to know if the way that I am determining the time zone of the users is correct/valid.
The way that i determine the TIME ZONE of the users is:
https://www.insum.ca/handling-time-zones-in-apex/
March 23, 2023 - 1:35 pm UTC
I don't know enough about APEX to comment on the solution you've found. It looks like it's based on the user's device settings; it's always possible for people to set this incorrectly. I would make it clear to users which time zone they're working in to reduce the chance of mistakes.
And as always - run your own tests to verify.