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.