Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tamas.

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

Answered by: Chris Saxon - Last updated: June 17, 2020 - 1:39 pm UTC

Category: SQL - Version: 11-12

Viewed 100+ times

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 we 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!

and you rated our response

  (4 ratings)

Reviews

Let me clarify

June 15, 2020 - 4:12 pm UTC

Reviewer: Tom

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

Followup  

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    

June 15, 2020 - 6:44 pm UTC

Reviewer: Tom

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

Followup  

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.

June 16, 2020 - 7:20 pm UTC

Reviewer: Tom

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

Followup  

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.

June 18, 2020 - 5:34 pm UTC

Reviewer: A reader

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.

More to Explore

PL/SQL

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