Home » SQL & PL/SQL » SQL & PL/SQL » Timezone Europe/Paris (10GR1 HPUX)
Timezone Europe/Paris [message #684307] Fri, 07 May 2021 01:49 Go to next message
davy7887
Messages: 1
Registered: May 2021
Junior Member
Hi all,

Could you explain why i have a differnce when i set Timezone please :




SQL> select to_timestamp(trunc(sysdate - 1)) from dual;

TO_TIMESTAMP(TRUNC(SYSDATE-1))
---------------------------------------------------------------------------
06-MAY-21 12.00.00 AM

SQL> Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
05-MAY-21 10.00.00 PM

SQL>
SQL>
SQL> ALTER SESSION SET TIME_ZONE = 'Europe/Paris';
Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;
Session altered.

SQL>

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
05-MAY-21 11.00.00 PM

SQL>

Re: Timezone Europe/Paris [message #684308 is a reply to message #684307] Fri, 07 May 2021 04:33 Go to previous messageGo to next message
John Watson
Messages: 8560
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

I'll try to help, but one of our European members can probably do better (I'm English, which means not really European anymore. Maintenant, nous sommes foutus.)

I think that your database is running on a server with OS configured to use central European time with daylight saving. Your first query is showing midnight CEST, ie, UTC + 2. Cool. Your second query converts that to UTC, and your third query converts it to CET, ie, UTC +1.
Re: Timezone Europe/Paris [message #684309 is a reply to message #684307] Fri, 07 May 2021 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is just because 10gR1 did not support DST (daylight saving time) and then 'Europe/Paris' was time zone +01:00 at any moment in the year.

So when you connect you got your session time zone from the OS: +02:00 and when you changed your session time zone to 'Europe/Paris' you were at +01:00.

(Note that your query implies implicit conversions from date to char then from char to timestamp so you were lucky you didn't get an error.)

[Updated on: Sun, 16 May 2021 14:46]

Report message to a moderator

Re: Timezone Europe/Paris [message #684310 is a reply to message #684307] Fri, 07 May 2021 19:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3063
Registered: January 2010
Location: Connecticut, USA
Senior Member
And how do you expect to get correct time zone results if sysdate is a date and has no time zone. So you are taking date that has no time zone, converting it to timestamp and timestamp has no time zone either. So obviously by changing session timezone you are getting different results - Oracle simply derives timezone from session timezone:

SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> ALTER SESSION SET TIME_ZONE = 'America/New_York';

Session altered.

SQL> Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
06-MAY-21 05.00.00 AM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/Paris';

Session altered.

SQL> Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
05-MAY-21 11.00.00 PM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';

Session altered.

SQL> Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
06-MAY-21 12.00.00 AM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/Athens';

Session altered.

SQL> Select sys_extract_utc(to_timestamp(trunc(sysdate - 1))) from dual;

SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-1)))
---------------------------------------------------------------------------
05-MAY-21 10.00.00 PM

SQL>
So when session timezone is America/New_York to_timestamp(trunc(sysdate - 1)) is assumed to be time in New York, when you change it to Europe/Paris Oracle assumes it it Paris time, and so on. If you want to get same UTC regardless of session timezone use FROM_TZ. I am in NY time zone, so:

SQL> ALTER SESSION SET TIME_ZONE = 'America/New_York';

Session altered.

SQL> Select sys_extract_utc(from_tz(to_timestamp(trunc(sysdate - 1)),'America/New_York')) from dual;

SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(TRUNC(SYSDATE-1)),'AMERICA/NEW_YORK'))
---------------------------------------------------------------------------
06-MAY-21 05.00.00 AM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/Paris';

Session altered.

SQL> Select sys_extract_utc(from_tz(to_timestamp(trunc(sysdate - 1)),'America/New_York')) from dual;

SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(TRUNC(SYSDATE-1)),'AMERICA/NEW_YORK'))
---------------------------------------------------------------------------
06-MAY-21 05.00.00 AM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';

Session altered.

SQL> Select sys_extract_utc(from_tz(to_timestamp(trunc(sysdate - 1)),'America/New_York')) from dual;

SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(TRUNC(SYSDATE-1)),'AMERICA/NEW_YORK'))
---------------------------------------------------------------------------
06-MAY-21 05.00.00 AM

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/Athens';

Session altered.

SQL> Select sys_extract_utc(from_tz(to_timestamp(trunc(sysdate - 1)),'America/New_York')) from dual;

SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(TRUNC(SYSDATE-1)),'AMERICA/NEW_YORK'))
---------------------------------------------------------------------------
06-MAY-21 05.00.00 AM

SQL>
SY.
Re: Timezone Europe/Paris [message #684351 is a reply to message #684309] Sun, 16 May 2021 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Timezone Europe/Paris [message #684352 is a reply to message #684351] Sun, 16 May 2021 14:30 Go to previous message
EdStevens
Messages: 1321
Registered: September 2013
Senior Member
In addition to the previous comments, note this. Given your statement:

select to_timestamp(trunc(sysdate - 1)) from dual;
Let's unpack that.

'sysdate' returns a DATE data type. This data type include 4-digit year, month, day, hour, minute, and second. The actual value returned is the current system date and time, as returned from a request to the OS by oracle. There is no knowledge of timezone.

'sysdate - 1' returns a DATE data type whose value is the current system date and time minus 1 day. So if the current date and time is May 16 2021 2:20 PM, this will return May 15 2021 2:20 PM.

trunc() takes a DATE data type and truncates out the time component. So if the value fed to trunc is May 15 2021 2:20 PM, the value returned will be May 15 2021 00:00.

Now it gets real fun

to_timestamp takes a string representation of a date/time and returns a TIMESTAMP data type. TIMESTAMP is like DATE in that it is an internal, binary data type. It includes date and time, down to the nano-second, and has variants that include time zone. Note that it takes a string representation as input. But SYSDATE returns a DATE, which is an internal binary structure. And so SYSDATE - 1 also returns a DATE. And trunc(sysdate - 1) also returns a DATE. But to_timestamp is expecting a STRING. So to satisfy TO_TIMESTAMP, oracle has to internally perform a to_char() on your 'trunc(sysdate -1)'. To do that, it will have to rely on the current session setting of NLS_DATE_FORMAT. Then when it gets a string it will have to rely on the current session setting of NLS_TIMESTAMP_FORMAT to fully construct the input for TO_TIMESTAMP. If your NLS_ settings are not perfeclty lined up, you will either get an error, or worse, no error but incorrect data.
Previous Topic: Optimization of SQL
Next Topic: Cents corrupted on large number
Goto Forum:
  


Current Time: Mon Jun 21 23:50:51 CDT 2021