Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tamas.

Asked: June 14, 2020 - 4:33 pm UTC

Last updated: January 20, 2025 - 8:47 am UTC

Version: 11-12

Viewed 10K+ times! This question is

You Asked

Trying to deal with timezone + DST aware datetime handling, but I couldn't get it safe and convenient at the same time.
The almost perfect solution would be timestamp with local time zone , except the fact that it uses nls_timestamp_format (opposed to using nls_timestamp_tz_format) which does not allow any timezone information to be formatted, thus the daylight saving information is lost during the conversion.
Although there _is_ time zone information, it just happens to be aligned to the local time zone (but it is not the _same_, as a Central European time zone is either CET, or CEST).

So for example a timestamp with local time zone -> char conversion (without explicitly specifying the format string at every single usage)
with European time zone could not distinguish '2020-10-25 02:30:00+02:00' from '2020-10-25 02:30:00+01:00' , as both will output '2020-10-25 02:30:00'.

Am I missing something very obvious?


with LiveSQL Test Case:

and Chris said...

I'm not entirely sure what your question is here. Perhaps this will help:

TIMESTAMP WITH TIME ZONE => Store values exactly as-is.

TIMESTAMP WITH LOCAL TIME ZONE => Normalize the value to the database's time zone. Return the data in the user's time zone.

So if the DB time zone is 00:00, the values stored in a TS with LTZ are:

2020-10-25 02:30:00+02:00 => 2020-10-25 00:30:00 UTC
2020-10-25 02:30:00+01:00 => 2020-10-25 01:30:00 UTC

At thirty-past midnight UTC, Brussels is still on daylight savings (CEST) => 2:30am in Brussels
At thirty-past one in the morning UTC, daylight savings has ended in Brussels (CET) => 2:30am in Brussels

This is why with local time zone reports the same hour for both: because they are the same hour. The calculation is daylight-savings aware!

When you specify +02:00, the database doesn't know which region you're in. It could be Belgium or South Africa (which doesn't observe daylight savings). The daylight savings information isn't there to lose!

Rating

  (8 ratings)

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

Comments

Let me clarify

Tom, June 15, 2020 - 4:12 pm UTC

I am aware that the calculation is daylight saving aware.
My question here tried to be:
How do I set a session or system default nls format, which contains the timezone and daylight saving information.

Please check the livesql I prepared with my question:
I've inserted two different dates (half an hour before and after a daylight saving change), but selecting them they become indistinguishable, as the nls_timestamp_format does not allow TZH, TZM, TZR or TZD, thus the DST information is lost during the timestamp->char default conversion.
Chris Saxon
June 15, 2020 - 5:27 pm UTC

Relying on NLS parameters is always putting you on shaky ground.

If you want a specific format, you should use to_char with the mask you want.

TZD tells you whether you're in daylight savings or not; you can EXTRACT the tz hour too if you want:

alter session set time_zone = 'Europe/Brussels';

create table tmp (  
  ts_ltz timestamp with local time zone  
);
insert into tmp values (timestamp '2020-10-25 02:30:00+02:00');
insert into tmp values (timestamp '2020-10-25 02:30:00+01:00');

select extract ( timezone_hour from ts_ltz ),
       to_char ( ts_ltz, 'TZD' )
from   tmp;

EXTRACT(TIMEZONE_HOURFROMTS_LTZ)   TO_CHAR(TS_LTZ,'TZD')   
                                  2 CEST                     
                                  1 CET    

Tom, June 15, 2020 - 6:44 pm UTC

I see and understand. I actually did that in the posted livesql script.

My question is if it can be done with a session or database level parameter, or it can not. In my opinion if one can not set a default nls format which does respect daylight saving changes, that is the shaky ground. I would consider a single, session (or database) level parameter set properly once much more robust and counter-shaky, than trying to make sure that the proper date format is set at every single usage.
Can it be done, or it can not? (and if not, shouldn't that be considered a bug, or at least a very strange feature?)

Thanks,
Tom (not the master)
Chris Saxon
June 16, 2020 - 12:34 pm UTC

In my opinion if one can not set a default nls format which does respect daylight saving changes

Is does respect daylight savings! That's why you get both values returning 2:30am.

This may not be what you're looking for, but it does respect the time changes.

I would consider a single, session (or database) level parameter set properly once much more robust and counter-shaky

You can set database-level settings. Session-level settings override these.

The problem with session-level settings is each individual session can set its own properties. I've seen cases where sysadmins have built a new set of servers with different country settings.

The app servers picked these up and the application behaved differently depending on which app server you connected to. To avoid this you need to explicitly to the conversion, not rely on implicit conversions.

That said, in most cases, SQL returns data to an application. And it's the application's job to handle the formatting. So rather than (implicitly or explicitly) converting timestamps to a string, you bind it to a timestamp type in the application. Then it's up to the app to display what's needed.

Tom, June 16, 2020 - 7:20 pm UTC

Please have a look at the attached livesql.

in the select, 3rd row, to_char(ts_ltz) - the timestamp with local time zone, the two returned rows are:
"2020-10-25 02:30:00.000"
"2020-10-25 02:30:00.000"
Both are the same, indistinguishable.
But the table does contain two different time values: one before a DST change and one after.
And according to the documentation - as far as I see - there is no way of fixing this with database, or session level configuration, as the format does not allow any timezone specific data.

It does respect daylight saving, but I find no way of getting the information out without specifying the date format inline (or of course - as you said - binding to a timestamp value, but that is not always possible, sometimes we just need to use char formatting).

I understand what is happening. I do know workarounds.
But my question remains: is it possible by any means to configure the database so that it will format a timestamp with local time zone into char without the data loss I described above, without specifying an inline format string?

The database can be read by multiple entities, being or not being aware of the importance of timezone and dst handling, so I would like to have a configuration which by default doesn't lose information during querying a timestamp value. Please.

Chris Saxon
June 17, 2020 - 1:39 pm UTC

I have and did look at the attached Live SQL script.

The NLS_TIMESTAMP_FORMAT doesn't support the time zone formats. If you want to see this changed, submit an enhancement request and the Database Ideas forum to see if there's community support for this:

https://community.oracle.com/community/groundbreakers/database/database-ideas

But I repeat - use explicit conversions!

If one client sets their format to

DD-MON-YYYY

and queries the timestamp as a string, how do you distinguish between any times on a given date?

If a client does/needs timestamp -> char conversion when fetching values, use an explicit format mask. Anything else is asking for trouble.

A reader, June 18, 2020 - 5:34 pm UTC

One can set a proper format using an explicit format conversion.
But the same can not be set as the default: the default conversion does not allow formats that would ensure data integrity.
Yes, in this context, relying on default format is asking for trouble.
But I find it a bit counter-logical reasoning that there is no need for a safe and complete default, because noone should be using the default.
But I understand that is is what it is, I didn't mean to change the world, I just wanted to be sure that I understood it all and there is no other way.
My question was answered,
thank you.

02:30 and 02:30 are not the same!

Marcus, January 16, 2025 - 5:48 am UTC

Of course, if one uses a format string which omits important information, the two values "02:30" and "02:30" look indistinguishable. But they could be made distinguishable by using a better formatting, for example with TZD which would make for "02:30 CEST" and "02:30 CET" which is absolutely distinguishable.

TIMESTAMP WITH LOCAL TIME ZONE, however, only stores the local time ("02:30") and does not store any bit about whether it was the first or second 02:30. So it is not only not allowed to use TZD together with LOCAL TIME ZONE - it is physically not possible.

In my opinion, TIMESTAMP WITH LOCAL TIME ZONE is neither better nor worse than TIMESTAMP. I can really not imagine any use case for it. If you want correct Time Zone handling, go with TIMESTAMP WITH TIME ZONE which internally stores UTC. Only with this type you will get correct values in each and every situation.
Connor McDonald
January 16, 2025 - 7:26 am UTC

In my opinion, TIMESTAMP WITH LOCAL TIME ZONE is neither better nor worse than TIMESTAMP. I can really not imagine any use case for it.

We don't force anyone to choose which particular timestamp datatype option they prefer - we simply give you the choice.

Some customers want to normalise all timestamps to the local timezone to yield consistency in their database no matter what the data client lobs over their way.

Others want to preserve the incoming timezone...

Choose what works best for your needs.

Fix: TZD is allowed, but gives wrong value

Marcus, January 16, 2025 - 6:08 am UTC

Sorry I have to correct my previous comment.

TZH isn't allowed with TIMESTAMP WITH LOCAL TIME ZONE, but, even worse, TZD is allowed but gives possibly wrong information.

Try this:
create table a(wtz timestamp with time zone, ltz timestamp with local time zone);
insert into a values(timestamp '2025-04-05 15:30:00 UTC' at local,timestamp '2025-04-04 15:30:00 UTC' at local);
insert into a values(timestamp '2025-04-05 16:30:00 UTC' at local,timestamp '2025-04-04 15:30:00 UTC' at local);
select to_char(wtz,'HH24:MI TZD'),to_char(ltz,'HH24:MI TZD'), dump(wtz),dump(ltz) from a;

(DB Time Zone in this case is Australia/Sydney)

My conclusion: NEVER EVER use TIMESTAMP WITH LOCAL TIME ZONE. I cannot imagine any use case for it.

Sooory for typo in example code

Marcus, January 16, 2025 - 6:16 am UTC

Damn that's embarrassing. Third post in a row. Perhaps if I had used my Oracle login, my posts were editable?

There's a typo in the previous example, sorry for that. Hopefully this one is correct:
create table a(wtz timestamp with time zone, ltz timestamp with local time zone);
insert into a values(timestamp '2025-04-05 15:30:00 UTC' at local,timestamp '2025-04-05 15:30:00 UTC' at local);
insert into a values(timestamp '2025-04-05 16:30:00 UTC' at local,timestamp '2025-04-05 16:30:00 UTC' at local);
select to_char(wtz,'HH24:MI TZD'),to_char(ltz,'HH24:MI TZD'), dump(wtz),dump(ltz) from a;

Connor McDonald
January 16, 2025 - 7:29 am UTC

Editing permitted if signed in is currently not possible but I like the idea of that.

We'll look at adding that functionality.

Simpler formulation

mathguy, January 16, 2025 - 4:09 pm UTC

I would ask a simpler question. It may very well have no answer.

Clearly timestamp with local time zone is time zone aware. So, when Oracle implemented the concept, including implicit conversion to string, why did it choose NLS_TIMESTAMP_FORMAT as the default format model, when it is supposed to produce a string representation of a timestamp with time zone?

The original poster hinted at this from the very beginning (but he also needs a workaround if there is one). Of course, "if we change it now we'll break existing code" and all that.
Chris Saxon
January 20, 2025 - 8:47 am UTC

It's because the value effectively is a timestamp - the time zone information is removed when you insert into TIMESTAMP WITH LOCAL TIME ZONE. When you fetch the values they're in the session time zone.

If you need to distinguish duplicate times when the clocks go back, you can use AT TIME ZONE to convert to a time zone without daylight savings or set the session time zone to one that doesn't observe DST.

e.g. the clocks go back at 2am on 26th Oct 2025 in the UK. so 00:30 UTC and 01:30 UTC are both 01:30 in London on this date.

alter session set time_zone='Europe/London';
insert into a values(
  timestamp '2025-10-26 00:30:00 UTC' at local,
  timestamp '2025-10-26 00:30:00 UTC' at local);
insert into a values(
  timestamp '2025-10-26 01:30:00 UTC' at local,
  timestamp '2025-10-26 01:30:00 UTC' at local);

select ltz from a;

LTZ
-----------------------------
26/10/2025 01:30:00.000000000
26/10/2025 01:30:00.000000000

select ltz at time zone 'UTC' from a;

LTZATTIMEZONE'UTC'
---------------------------------
26/10/2025 00:30:00.000000000 UTC
26/10/2025 01:30:00.000000000 UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library