Skip to Main Content
  • Questions
  • converting a table from a timezone to another timezone

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, zoulfikar.

Asked: March 17, 2023 - 10:52 am UTC

Last updated: March 23, 2023 - 1:36 pm UTC

Version: 22.22

Viewed 10K+ times! This question is

You Asked

I have a table with two columns START_DATE and END_DATE and they have a data type of : Timestamp with timezone.
and i have also a Globale Item that get the timezone of the user, and i should use it to convert the timezone of the column in the database to the timezone of the user.

the data in my table are stored in this format:
09-MAR-23 06.30.00.000000 PM +01:00


the way i do the insert is:

insert into table_name(START_DATE) VALUES(TO_TIMESTAMP(:P51010_START_DATE, 'YYYY-MM-DD"T"HH24:MI:SS'););

My attempts to solve the proble:
1. first attempt:
SELECT 
   CAST(START_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_START_DATE,
   CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_END_DATE
FROM TABLE_NAME;


result:
ORA-20999: Failed to parse SQL query! ORA-06550: line 6, column 83: ORA-00923: FROM keyword not found where expected

2. second attempt:
SELECT
  CAST(  data_inizio AS TIMESTAMP WITH TIME ZONE ) AT TIME ZONE   '''' || '' || :GP_TMZ || ''''   as CONVERTED_START_DATE,
  CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE  '''' || '' || :GP_TMZ || ''''  AS CONVERTED_END_DATE
FROM TABLE_NAME;


To add a single quote at the Begining and at the end of the page item i try this query
result:
ORA-20999: Failed to parse SQL query! ORA-06550: line 1, column 1: ORA-01882: timezone region not found




Shared Components: Globalization section

Application Timestamp Format: DD-MON-YYYY HH:MI:SSXFF PM
Application Timestamp Time Zone Format DD-MON-YYYY HH.MI.SSXFF PM TZR

select * from v$version;
BANNER: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.1.0
BANNER_LEGACY: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID: 0

and we said...

The first attempt fell victim to a bug. A bind variable reference following AT TIME ZONE confuses the parser. I filed bug 35197079 to report this issue.

There seems to be a simple workaround. Just use:

SELECT 
   CAST(START_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE NULL||:GP_TMZ AS CONVERTED_START_DATE,
   CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE NULL||:GP_TMZ AS CONVERTED_END_DATE
FROM TABLE_NAME;


On the other hand, if the columns are of the data type TIMESTAMP WITH TIME ZONE, as you wrote, the CAST is redundant and confusing. This should be sufficient:

SELECT 
   START_DATE AT TIME ZONE NULL||:GP_TMZ AS CONVERTED_START_DATE,
   END_DATE AT TIME ZONE NULL||:GP_TMZ AS CONVERTED_END_DATE
FROM TABLE_NAME;


If the columns are actually of the data type DATE or TIMESTAMP, then the query would be wrong because CAST would associate the SESSIONTIMEZONE with the column values before transforming the values to the desired time zone. As the SESSIONTIMEZONE may differ between sessions, such a dynamic time zone associated with stored (static) column values would probably be wrong. The FROM_TZ function can explicitly associate an application-specific time zone to the timezoneless values in the DATE and TIMESTAMP columns.

Your second attempt had no chances because any expression after AT TIME ZONE that starts with a text literal needs to be enclosed in parentheses to be parsed as a whole. This is an ambiguity in the syntax that we need to fix or carefully document. Also, if you concatenate quotes into the time zone value, the value will become illegal. Quotes should only mark a literal and not be part of the value.


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Zoulfikar, March 20, 2023 - 8:58 am UTC

Thank you for your time
Chris Saxon
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.
Chris Saxon
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

Chris Saxon
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).
Chris Saxon
March 22, 2023 - 2:32 pm UTC

Good suggestion

timestamp time zone

zoulfikar, March 22, 2023 - 7:58 am UTC

The way I determine the time zone of the users is through a dynamic action during page load, using the method described on this webpage: https://www.insum.ca/handling-time-zones-in-apex/

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?
Chris Saxon
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);

Chris Saxon
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/
Chris Saxon
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.


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.