How to show date in local time zone in Oracle?-CodePudding

I have a database used by users in different timezones.

The database server is let's say in 01:00 timezone.

Here is my need:

When User A in TZ 01:00 inserts a date/time, for example August 25, 2022 16h30m it should be stored in the database as August 25, 2022 16h30m

When User B in TZ 00:00 inserts August 25, 2022 16h30m, it should be stored in the database as August 25, 2022 17h30m. And whenever he is connected, he should always see August 25, 2022 16h30m

User Region       value inserted      What he sees        what is stored in DB
------------------------------------------------------------------------------
 01:00            Aug 25, 22 16h30m   Aug 25, 22 16h30m   Aug 25, 22 16h30m
 00:00            Aug 25, 22 16h30m   Aug 25, 22 16h30m   Aug 25, 22 17h30m

I tried all different Date formats like this:

alter table SomeTable add(Col_A_Date DATE);
alter table SomeTable add(Col_B_TimeStamp TIMESTAMP);
alter table SomeTable add(Col_C_TimeStampWithTimeZone TIMESTAMP WITH TIME ZONE);
alter table SomeTable add(Col_D_TimeStampWithLocalTimeZone TIMESTAMP WITH LOCAL TIME ZONE);

-- Assuming it's User A : set time zone to GMT   1
alter session set time_zone=' 1:00';

-- Inserting data in the table as User A
insert into SomeTable (Col_A_Date, Col_B_TimeStamp, Col_C_TimeStampWithTimeZone, Col_D_TimeStampWithLocalTimeZone) values (sysdate, sysdate, sysdate, sysdate);

-- Query data
select * from SomeTable;

Result :

-- Now assuming it's User B who is in another region : Changing session to GMT
alter session set time_zone=' 0:00';

-- Query data. Here I expect user B to see different values
select * from SomeTable;

Result :

As seen in the results, nothing changes in the display of values for all the timestamp columns.

Does anyone know which datatype should I use to store my dates and how to show them automatically based on the region the user is connected from?

CodePudding user response：

Based on your requirements I would suggest data type TIMESTAMP WITH LOCAL TIME ZONE

For this data type the data is stored in DBTIMEZONE - which can set to 01:00, if you prefer. The data is always shown in current user session time zone, i.e. at SESSIONTIMEZONE

Note, changing DBTIMEZONE requires a database restart. You cannot modify DBTIMEZONE if you have already a table with a TIMESTAMP WITH LOCAL TIME ZONE column which contains any data.

Your problem is SYSDATE. SYSDATE returns a DATE value in the time zone of database server's operating system. The DATE value itself does not contain any time zone information, Oracle defaults it to SESSIONTIMEZONE.

Try

insert into SomeTable (
   Col_A_Date, 
   Col_B_TimeStamp, 
   Col_C_TimeStampWithTimeZone, 
   Col_D_TimeStampWithLocalTimeZone) 
values (
  CURRENT_DATE, 
  LOCALTIMESTAMP, 
  CURRENT_TIMESTAMP, 
  CURRENT_TIMESTAMP);

Then the output should be what you expect.