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.
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)
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.
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.
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;
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.
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