CODEWITHSUNDEEP
oracle-databasedatedatetimetimezone
ZygD
ZygD

Reputation: 24498

Which time zone does the table's column of DATE data type reflect?

In Oracle database I have this table (the data type of column col is DATE):

col
2021-02-26 23:14:24

Question: in my case, assuming over time DB settings haven't changed, which time zone does the table's column of DATE data type reflect?

Following I provide current DB settings.

SELECT DBTIMEZONE FROM DUAL;

|DBTIMEZONE  |
|============|
|+00:00      |

SELECT SESSIONTIMEZONE FROM DUAL;

|SESSIONTIMEZONE|
|===============|
|Europe/Helsinki|

SELECT CURRENT_DATE FROM DUAL;

|CURRENT_DATE       |
|===================|
|2021-07-18 15:05:32|

SELECT SYSDATE FROM DUAL;

|SYSDATE            |
|===================|
|2021-07-18 15:05:32|

SELECT SYSTIMESTAMP FROM DUAL;

|SYSTIMESTAMP                  |
|==============================|
|2021-07-18 15:05:32.984 +03:00|

Upvotes: 0

Views: 935

Answers (2)

MT0
MT0

Reputation: 168701

The time zone is undefined by the column.

You can do:

CREATE TABLE table_name (col DATE);

ALTER SESSION SET TIME_ZONE = 'UTC';
INSERT INTO table_name (col) VALUES (CURRENT_DATE);
ALTER SESSION SET TIME_ZONE = 'Europe/Helsinki';
INSERT INTO table_name (col) VALUES (CURRENT_DATE);
ALTER SESSION SET TIME_ZONE = 'America/New_York';
INSERT INTO table_name (col) VALUES (CURRENT_DATE);

Then:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM table_name;

Outputs:

COL
2021-07-18 21:06:05
2021-07-19 00:06:05
2021-07-18 17:06:05

db<>fiddle here

Those values were all inserted in the same second by the same user in the same session using identical SQL statements; however there is no consistency in the time zone of the DATE value as the session settings were altered between each statement; so you cannot rely on a DATE having any particular time zone.

If you want to work out what time zone your data is in then check your application that is storing the data:

  • Is it always using SYSDATE? Then the time zone of the column is the time zone of the database's system.
  • Does the application specify the time zone? Then the data will have the time zone specified by the application.
  • Is it taking data from an external source? Then check that external source.
  • Is it taking data from the user? Then you have no guarantees.

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

If the data is of type DATE, then it doesn't reflect any time zone at all. It's just a date and time, with time resolved to the second. SYSDATE simply gets date and time from the host server OS, so to the degree that the time portion is reflective of any time zone, it would be that of the host OS.

Upvotes: 0

Related Questions