Home » SQL & PL/SQL » SQL & PL/SQL » Different UTC time from SYSDATE on different databases (Oracle 21c/19c)
Different UTC time from SYSDATE on different databases [message #687425] Wed, 08 March 2023 08:56 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi DBAs,
I've come across a strange phenomenon and admit I'm a bit stuck, probably misunderstanding or misusing some component's features..
Maybe someone can shed some light on this..

It's worth mentioning that according to Google - it's 14:32 UTC now.

What I want to do: to convert a date, such as SYSDATE to the correct UTC time for display



On SERVER1 19c:


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi';

Session altered.

SQL> col stz for a10
SQL> col dbtz for a10
SQL> col curr_date for a16
SQL> col sysdate for a16
SQL> col utc_time for a16
SQL> select to_char(sys_extract_utc(systimestamp),'dd.mm.yyyy hh24:mi') utc_time,to_char(current_date,'dd.mm.yyyy hh24:mi') curr_date,
  2  to_char(sysdate,'dd.mm.yyyy hh24:mi') "sysdate",dbtimezone dbtz,sessiontimezone stz from dual;

UTC_TIME         CURR_DATE        sysdate          DBTZ       STZ
---------------- ---------------- ---------------- ---------- ----------
08.03.2023 14:42 09.03.2023 01:42 08.03.2023 14:42 +00:00     +11:00


Now on Server2:

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi';

Session altered.

SQL> col stz for a10
SQL> col dbtz for a10
SQL> col curr_date for a16
SQL> col sysdate for a16
SQL> col utc_time for a16
SQL> select to_char(sys_extract_utc(systimestamp),'dd.mm.yyyy hh24:mi') utc_time,to_char(current_date,'dd.mm.yyyy hh24:mi') curr_date,
  2  to_char(sysdate,'dd.mm.yyyy hh24:mi') "sysdate",dbtimezone dbtz,sessiontimezone stz from dual;

UTC_TIME         CURR_DATE        sysdate          DBTZ       STZ
---------------- ---------------- ---------------- ---------- ----------
08.03.2023 14:33 08.03.2023 16:33 08.03.2023 16:33 +00:00     +02:00

So far so good..

PROBLEM is that with any of these, I cannot get a consistent correct result on both instances with the right UTC time.

I tried


select from_tz (CAST (sysdate AS TIMESTAMP),'UTC') as UTC_DATE from dual;
select from_tz ((CAST (sysdate AS TIMESTAMP)),dbtimezone) as UTC_DATE from dual;
select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),sessiontimezone ) as UTC_DATE from dual;
select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),dbtimezone ) as UTC_DATE from dual;
select sys_extract_utc(cast ( sysdate as timestamp) ) as UTC_DATE from dual;

On SERVER1 I get:

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL> select from_tz (CAST (sysdate AS TIMESTAMP),'UTC') as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.46.39.000000 PM UTC

SQL> select from_tz ((CAST (sysdate AS TIMESTAMP)),dbtimezone) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.46.39.000000 PM +00:00

SQL> select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),sessiontimezone ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 03.46.39.000000 AM +11:00

SQL> select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),dbtimezone ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 03.46.39.000000 AM +00:00

SQL> select sys_extract_utc(cast ( sysdate as timestamp) ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 03.46.39.000000 AM

SQL>


On Server 2:


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> select from_tz (CAST (sysdate AS TIMESTAMP),'UTC') as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 04.47.40.000000 PM UTC

SQL> select from_tz ((CAST (sysdate AS TIMESTAMP)),dbtimezone) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 04.47.40.000000 PM +00:00

SQL> select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),sessiontimezone ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.47.40.000000 PM +02:00

SQL> select from_tz (sys_extract_utc(cast ( sysdate as timestamp) ),dbtimezone ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.47.40.000000 PM +00:00

SQL> select sys_extract_utc(cast ( sysdate as timestamp) ) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.47.40.000000 PM

SQL>





I can get the correct UTC time from SYSDATE On Server 1 with any of these:

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL> select from_tz (CAST (sysdate AS TIMESTAMP),'UTC') as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.50.26.000000 PM UTC

SQL> select from_tz ((CAST (sysdate AS TIMESTAMP)),dbtimezone) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 02.50.27.000000 PM +00:00

SQL>
But same commands give the wrong time on Server 2:

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> select from_tz (CAST (sysdate AS TIMESTAMP),'UTC') as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 04.51.58.000000 PM UTC

SQL> select from_tz ((CAST (sysdate AS TIMESTAMP)),dbtimezone) as UTC_DATE from dual;

UTC_DATE
---------------------------------------------------------------------------
08-MAR-23 04.51.59.000000 PM +00:00

SQL>

What am I doing wrong, and how can I get a consistent correct value of UTC TIME for a given SYSDATE ( Important that it will be from a date data type ) ?

Thanks in advance
Andrey
Re: Different UTC time from SYSDATE on different databases [message #687426 is a reply to message #687425] Wed, 08 March 2023 09:21 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
From your attempts, it is clear that you understand SYSDATE is just a date-time without knowledge of the time zone it which it is supposed to be, and you are first trying to convert it to have the proper time zone before changing to UTC.

The problem is that SYSDATE, and also SYSTIMESTAMP, is not in a time zone specific to the database, like DBTIMEZONE, etc. Rather, both are in the time zone of the operating system on the computer where the database is running. Sometimes that is the same as DBTIMEZONE, but sometimes it isn't. SYSTIMESTAMP "knows" what time zone it is in: when the value is retrieved by the database engine, both the date and time components AND the time zone are passed to the DB. By contrast, when the DB engine requests SYSDATE from the operating system, only the date and time component are passed back; there is no way to figure out, after the fact, what the time zone was.

So - there is no way to do what you want, starting just from SYSDATE and using database settings like DBTIMEZONE, SESSIONTIMEZONE or UTC.

Note that on a traveling laptop, the operating system time zone may be set up so that it always changes to the "local" time zone (if I am in Los Angeles the time zone will be PDT; if I travel to New York, it will change to EDT).

The best option is to always use SYSTIMESTAP for such things, not SYSDATE.
Re: Different UTC time from SYSDATE on different databases [message #687427 is a reply to message #687425] Wed, 08 March 2023 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From doc: "SYSDATE returns the current date and time set for the operating system on which the database server resides".
It has no time zone information and is neither in db time zone nor session time zone.

You cannot extract UTC from SYSDATE unless you know your operating system time zone which you can know using SYSTIMESTAMP: "SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE."

Re: Different UTC time from SYSDATE on different databases [message #687428 is a reply to message #687427] Wed, 08 March 2023 09:35 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Many thanks both, that is very enlightening.

So to follow up, what if I need to know at what point in time UTC a logon happened, from column LOGON_TIME V$session that is described as DATE

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESSION.html

What would be possible to figure out at what point in time it happened ?

Regards,
Andrey
Re: Different UTC time from SYSDATE on different databases [message #687429 is a reply to message #687428] Wed, 08 March 2023 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Without knowing Oracle internal code it is hard to give a definitive answer.
As it is a very old view, before time zones were introduced in Oracle code, we can assume that this date comes from SYSDATE (or the associated system call), and so back to the previous question.

Assuming the operating system time zone is not changed during an Oracle instance, you can get the time zone from SYSTIMESTAMP.

SQL> select to_char(systimestamp,'TZR') from dual;
TO_CHAR(SYSTIMESTAMP,'TZR')
--------------------------------
+01:00

[Updated on: Wed, 08 March 2023 10:52]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687430 is a reply to message #687425] Wed, 08 March 2023 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am on Oracle 12c, so I cannot test this, but I see you are on 21c and 19c, where there is a TO_UTC_TIMESTAMP_TZ function that accepts a string representing a DATE with time, that you can get using TO_CHAR on v$session.logon_time and returns a timestamp with UTC timezone.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_UTC_TIMESTAMP_TZ.html#GUID-1728EE3E-EC0C-4FA8-B404-99C0A445CE8 2

You may have to concatenate the timezone extracted from systimestamp as demonstrated by Michel, in order for it to know what timezone to convert it from.

[Updated on: Wed, 08 March 2023 11:19]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687431 is a reply to message #687430] Wed, 08 March 2023 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right (didn't know this function):
MIKJ3DB1> select TO_UTC_TIMESTAMP_TZ(to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS')), sysdate from dual;
TO_UTC_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:SS'))             SYSDATE
--------------------------------------------------------------------------- -------------------
08/03/2023 18:25:25.000 +00:00                                              08/03/2023 18:25:25

1 row selected.
So there it just assume SYSDATE is from UTC (as stated by the doc: "the new function assumes that the input string uses the ISO 8601 date format, defaulting the time zone to UTC 0." but concatenating with SYSTIMESTAMP time zone that works well:
MIKJ3DB1> select TO_UTC_TIMESTAMP_TZ(
  2           to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS')||
  3           TO_CHAR(SYSTIMESTAMP,'TZR')),
  4         sysdate
  5  from dual;
TO_UTC_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:SS')||TO_CHAR(SYS SYSDATE
--------------------------------------------------------------------------- -------------------
08/03/2023 18:28:04.000 +01:00                                              08/03/2023 18:28:04
And then using SYS_EXTRACT_UTC you get the final result:
MIKJ3DB1> select sys_extract_utc(
  2           TO_UTC_TIMESTAMP_TZ(
  3             to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS')||
  4             TO_CHAR(SYSTIMESTAMP,'TZR'))),
  5         sysdate
  6  from dual;
SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:S SYSDATE
--------------------------------------------------------------------------- -------------------
08/03/2023 17:38:25.000                                                     08/03/2023 18:38:25

[Updated on: Wed, 08 March 2023 11:39]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687432 is a reply to message #687429] Wed, 08 March 2023 12:07 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
The problem is that LOGON_TIME is of DATE data type, without time zone info. Perhaps the reason for that is two-fold: first, V$SESSION existed long before Oracle started support for time zones, and after they added that support, they didn't change the specification of V$SESSION so that it works consistently across versions. This last part is weak - they could have kept LOGON_TIME as is but added a LOGON_TIMESTAMP column to store the complete info including time zone. They didn't.

To make it worse, the documentation of V$SESSION, at least as far as I was able to find, only states that LOGON_TIME is of DATE data type and is "Time of logon". At the very least, they could tell us if the time of logon is at "system" time zone (from the operating system running the server), or at SESSIONTIMEZONE, or some other time zone. At least the system time zone and SESSIONTIMEZONE make sense in this context, and in general we can't assume they are equal.

So - first, you need to find someone who knows if LOGON_TIME is supposed to be at "system" time zone or at SESSIONTIMEZONE, or something else. Then you can take it from there; the rest is not very complicated.
Re: Different UTC time from SYSDATE on different databases [message #687433 is a reply to message #687432] Wed, 08 March 2023 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Well, it seems you live in an ideal world. Smile
In Oracle one, when some code works it is not changed (nor investigated).

And to answer the question:
SQL> conn michel/michel
Connected.

SQL> select logon_time, sysdate, dbtimezone, sessiontimezone, systimestamp
  2  from v$session
  3  where sid=(select sid from v$mystat where rownum=1)
  4  /
LOGON_TIME          SYSDATE             DBTIME SESSIONTIMEZONE SYSTIMESTAMP
------------------- ------------------- ------ --------------- ------------------------------
08/03/2023 20:33:54 08/03/2023 20:33:54 +02:00 +10:00          08/03/2023 20:33:54.781 +01:00
Re: Different UTC time from SYSDATE on different databases [message #687434 is a reply to message #687433] Wed, 08 March 2023 19:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Simplest way is to audit logon/logoff:

AUDIT SESSION
/
Now you can use column EXTENDED_TIMESTAMP in DBA_AUDIT_TRAIL:

SELECT  USERNAME,
        EXTENDED_TIMESTAMP AT TIME ZONE 'UTC'
  FROM  DBA_AUDIT_TRAIL
  WJERE ACTION_NAME = 'LOGON'
/
And if you use unified audit then you could use EVENT_TIMESTAMP_UTC which would give you exactly what you need.

SY.
Re: Different UTC time from SYSDATE on different databases [message #687435 is a reply to message #687434] Wed, 08 March 2023 19:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
In addition audit trail would also give you failed logins (RETURNCODE != 0).

SY.
Re: Different UTC time from SYSDATE on different databases [message #687436 is a reply to message #687434] Thu, 09 March 2023 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

...Assuming that audit is activated and logons are audited.

Re: Different UTC time from SYSDATE on different databases [message #687437 is a reply to message #687436] Thu, 09 March 2023 02:49 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Solomon Yakobson wrote on Thu, 09 March 2023 03:05
Simplest way is to audit logon/logoff:

AUDIT SESSION
/
Now you can use column EXTENDED_TIMESTAMP in DBA_AUDIT_TRAIL:

SELECT  USERNAME,
        EXTENDED_TIMESTAMP AT TIME ZONE 'UTC'
  FROM  DBA_AUDIT_TRAIL
  WJERE ACTION_NAME = 'LOGON'
/
And if you use unified audit then you could use EVENT_TIMESTAMP_UTC which would give you exactly what you need.

SY.

Michel Cadot wrote on Thu, 09 March 2023 08:21

...Assuming that audit is activated and logons are audited.
Right. But sometimes when you want to standardize an approach to different shops, you don't have the luxury of standardizing all shops to have auditing on,
yet the requirement to have the correct logon time information with any localization settings remains.


So I had a look at the technique you nicely demonstrated of combining the timezone offset of the SYSTIMESTAMP and the date without any timezone info recorded:

Michel Cadot wrote on Wed, 08 March 2023 19:36
You're right (didn't know this function):
...
..
..
And then using SYS_EXTRACT_UTC you get the final result:
MIKJ3DB1> select sys_extract_utc(
  2           TO_UTC_TIMESTAMP_TZ(
  3             to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS')||
  4             TO_CHAR(SYSTIMESTAMP,'TZR'))),
  5         sysdate
  6  from dual;
SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:S SYSDATE
--------------------------------------------------------------------------- -------------------
08/03/2023 17:38:25.000                                                     08/03/2023 18:38:25
And from this, I take it as best approach in this situation will be:

To *assume* that the timezone offset appearing currently in SYSTIMESTAMP subtracted/added from/to the LOGON_TIME will give the correct LOGON_TIME_UTC.

I tried it on Server 1 ( 19c) :


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> set lines 900 pages 20000
SQL> COL LOGON_TIME_LOCAL_DB_TZ FOR A35
SQL> COL LOGON_TIME_UTC FOR A35
SQL> COL SYSTIMESTAMP FOR A35
SQL> COL DBTIMEZONE FOR A22
SQL> COL SESSIONTIMEZONE FOR A22
SQL> --
SQL> select sys_extract_utc(
  2           TO_UTC_TIMESTAMP_TZ(
  3             to_char(logon_time,'YYYY-MM-DD"T"HH24:MI:SS')||
  4             TO_CHAR(SYSTIMESTAMP,'TZR'))) as LOGON_TIME_UTC,
  5         logon_time AS LOGON_TIME_LOCAL_DB_TZ,
  6     SYSTIMESTAMP,DBTIMEZONE,SESSIONTIMEZONE
  7  from v$session where sid=userenv('sid');

LOGON_TIME_UTC                      LOGON_TIME_LOCAL_DB_TZ              SYSTIMESTAMP                     DBTIMEZONE                SESSIONTIMEZONE
----------------------------------- ----------------------------------- ----------------------------------- ---------------------- ----------------------
09-MAR-23 08.39.13.000000000 AM     09.03.2023 08:39:13                 09-MAR-23 08.39.16.857900 AM +00:00 +00:00                 +11:00

SQL>
Then on Server 2 ( 21c ):

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> set lines 900 pages 20000
SQL> COL LOGON_TIME_LOCAL_DB_TZ FOR A35
SQL> COL LOGON_TIME_UTC FOR A35
SQL> COL SYSTIMESTAMP FOR A35
SQL> COL DBTIMEZONE FOR A22
SQL> COL SESSIONTIMEZONE FOR A22
SQL> --
SQL> select sys_extract_utc(
  2           TO_UTC_TIMESTAMP_TZ(
  3             to_char(logon_time,'YYYY-MM-DD"T"HH24:MI:SS')||
  4             TO_CHAR(SYSTIMESTAMP,'TZR'))) as LOGON_TIME_UTC,
  5         logon_time AS LOGON_TIME_LOCAL_DB_TZ,
  6     SYSTIMESTAMP,DBTIMEZONE,SESSIONTIMEZONE
  7  from v$session where sid=userenv('sid');

LOGON_TIME_UTC                      LOGON_TIME_LOCAL_DB_TZ              SYSTIMESTAMP                        DBTIMEZONE             SESSIONTIMEZONE
----------------------------------- ----------------------------------- ----------------------------------- ---------------------- ----------------------
09-MAR-23 08.40.07.000000000 AM     09.03.2023 10:40:07                 09-MAR-23 10.40.09.550000 AM +02:00 +00:00                 +02:00

SQL>
I got the right UTC Time for LOGON_TIME.

I then changed the local time of the server 2 ( 21c) to be Arizona (-07:00 ), rebooted, and tested the same:



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6408894200 bytes
Fixed Size                  9869048 bytes
Variable Size            1593835520 bytes
Database Buffers         4798283776 bytes
Redo Buffers                6905856 bytes
Database mounted.
Database opened.
SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> set lines 900 pages 20000
SQL> COL LOGON_TIME_LOCAL_DB_TZ FOR A35
SQL> COL LOGON_TIME_UTC FOR A35
SQL> COL SYSTIMESTAMP FOR A35
SQL> COL DBTIMEZONE FOR A22
SQL> COL SESSIONTIMEZONE FOR A22
SQL> --
SQL> select sys_extract_utc(
  2           TO_UTC_TIMESTAMP_TZ(
  3             to_char(logon_time,'YYYY-MM-DD"T"HH24:MI:SS')||
  4             TO_CHAR(SYSTIMESTAMP,'TZR'))) as LOGON_TIME_UTC,
  5         logon_time AS LOGON_TIME_LOCAL_DB_TZ,
  6     SYSTIMESTAMP,DBTIMEZONE,SESSIONTIMEZONE
  7  from v$session where sid=userenv('sid');

LOGON_TIME_UTC                      LOGON_TIME_LOCAL_DB_TZ              SYSTIMESTAMP                        DBTIMEZONE             SESSIONTIMEZONE
----------------------------------- ----------------------------------- ----------------------------------- ---------------------- ----------------------
09-MAR-23 08.42.23.000000000 AM     09.03.2023 01:42:23                 09-MAR-23 01.42.28.960000 AM -07:00 +00:00                 -07:00

SQL>
This also gave me the right LOGON_TIME_UTC


Very nice.

Conclusion-Assumption ( maybe something between that and a skilled guess.. ) :

Dates are recorded in date columns of DMVs as local times per the server's local time that has correlation to current timezone , but not with no record of which timezone, however, if no change occurred to the local timezone - it can be converted for display as UTC by correlating it with the timezone from SYSTIMESTAMP


Is this conclusion correct in your opinion ?
Anything else important to take into account for best accuracy ( without turning extra features on ) ?

Thanks
Andrey

[Updated on: Thu, 09 March 2023 02:51]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687441 is a reply to message #687437] Thu, 09 March 2023 09:52 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
What you describe is exactly right. Michel's answer to me demonstrated that, indeed, LOGON_TIME is given at the time zone of the db server's operating system (the timestamp embedded in SYSTIMESTAMP).

I never understood why Oracle maintains functions for DBTIMEZONE and SESSIONTIMEZONE but not SYSTIMEZONE; we have SYSDATE, SYSTIMESTAMP, why do we have to jump through hoops to get SYSTIMEZONE? The issue can't be "because it's an OS value, not maintained by the database"; the same would apply to SESSIONTIMEZONE.

The only thing I am not convinced of is the usefulness of TO_UTC_TIMESTAMP_TZ in this context. I would get the UTC time like this:

sys_extract_utc(from_tz(cast(logon_time as timestamp), to_char(systimestamp, 'tzr')))
FROM_TZ takes a timestamp (without time zone) and a time zone specification in string format, and returns a timestamp with time zone, where the date-time component and the time zone are those given as arguments. We use TO_CHAR(SYSTIMESTAMP, 'TZR') to return the system time zone. Alas, the FROM_TZ function insists on the first argument actually being a timestamp; while Oracle does many implicit conversions, in this case it decided not to, so we need the explicit CAST as I show above. Then SYS_EXTRACT_UTC is used as before.

[Updated on: Thu, 09 March 2023 09:53]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687442 is a reply to message #687437] Thu, 09 March 2023 09:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to explain what are you trying to do. Pretty much every application nowadays uses connection pooling, so you can't track application user logon time via database logons. All you will be able to track is direct logins (developers, support, qa testers, etc.). And how accurate do you need it to be? Even if you will be tracking logon time for today you still need to account for DST. Assume today is clock change day. So you'd need to adjust logon time for sessions that logged in today but before clock change. And you'd have to track for any DST law changes in localities where your databases are. That's why turning on session audit makes it so much simpler - all you need if apply PSU patches that already have all DST changes.

SY.

[Updated on: Thu, 09 March 2023 09:54]

Report message to a moderator

Re: Different UTC time from SYSDATE on different databases [message #687444 is a reply to message #687442] Thu, 09 March 2023 10:02 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Solomon Yakobson wrote on Thu, 09 March 2023 17:52
You need to explain what are you trying to do. Pretty much every application nowadays uses connection pooling, so you can't track application user logon time via database logons. All you will be able to track is direct logins (developers, support, qa testers, etc.). And how accurate do you need it to be? Even if you will be tracking logon time for today you still need to account for DST. Assume today is clock change day. So you'd need to adjust logon time for sessions that logged in today but before clock change. And you'd have to track for any DST law changes in localities where your databases are. That's why turning on session audit makes it so much simpler - all you need if apply PSU patches that already have all DST changes.

SY.
Thanks Solomon for that note. I agree that connection pooling would make it impossible to audit logons accurately,
However, it was mostly an example for some info of data type date, that one may want to see a UTC interpretation of.

I think that it will still be valuable in other cases, such as SQL_EXEC_START and other date columns that describe actions in the database, and there is no TIMESTAMP type of column to do the same.


Regards,
Andrey
Previous Topic: Identify Groups where 2 or more Clients have bought the same Products - SQL Oracle
Next Topic: XML parsing performance
Goto Forum:
  


Current Time: Fri Mar 29 01:21:42 CDT 2024