Home » SQL & PL/SQL » SQL & PL/SQL » correct treatment of date across different timezones (Oracle 19)
correct treatment of date across different timezones [message #689733] Thu, 04 April 2024 03:19 Go to next message
jansulc
Messages: 4
Registered: April 2024
Junior Member
Hello all.
we have a database, until now located in Germany
another database, also located in Germany, is sending data to us, and inserts some additional status info into our table (over db link)

something like:
insert into status_table (name_of_delivery, status_flag, insertion_date)
values ('Delivery ABC', 'Transfer finished', sysdate);

now we moved to Oracle 19, located in London

which caused big trouble, since the "sysdate" inserted is now false, it is one hour ahead (since Germany has 1+ against London)

question 1
how should they (German database) change their insert statement?
something like this?
first, find out what time it is, in the target database:
   SELECT CAST((CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London') AS DATE) dt from dual;
...and then use it instead of "sysdate" in their insert stmt?

question 2
is it possible / advisable to use more general/dynamic approach?
the solution above relies on the fact, that target database team always informs all other database teams about change of location, and they change their code
wouldn't it be better, if:
database, which sends "sysdate" via db-link into other database, always uses more "sofisticated" code, which:
1. finds out the target db timezone - select dbtimezone from dual@remote_db
2. combines this dbtimezone value with the sysdate and uses this value in the "insert statement"

Then, even if the target database moves to different location, and they forget to inform other teams about it, everything works fine for everybody, without a need to change any code, right?

question 3
if my idea above is correct, can you please give me some hints what syntax to use?
so far I discovered this possibility:

with t1(tm) as
(select systimestamp from dual
)
select tm as timedate_with_diff_against_UTC
      ,cast(to_timestamp_tz(tm, 'dd-mon-yy hh24.mi.ss.ff TZH:TZM') as date) as into_date
      ,(select dbtimezone from dual) as diff_against_GMT      
       --dbtimezone info is selected from remote/target db:
      ,cast(to_timestamp_tz(tm, 'dd-mon-yy hh24.mi.ss.ff TZH:TZM') at time zone ((select dbtimezone from dual)-(select dbtimezone from dual@remote_db)) 
         as date) as dynamic_TZ
  from t1;
But:
- if I am correct, systimestamp shows shift against UTC
 UTC is used by nobody, is does not reflect summer time shifts.
- better is "dbtimezone" info - it reflects also summer time
- my idea is: select our local dbtimezone, substract it from target database dbtimezone and use the value in the "at time zone" clause
But is it good idea to do such a substraction?
Like the one above:
(select dbtimezone from dual)-(select dbtimezone from dual@remote_db)  
(...but, it cannot be done exactly like this, it creates issue with NUMBER/CHAR inconsistent datatypes.
Don't know if this a right direction - if yes, I will try to solve this datatype issue.)
I think there must be some general solution, some "good practice" way, how to manage this, but couldn't find any in the documentation.

Thanks for any hint.
Re: correct treatment of date across different timezones [message #689734 is a reply to message #689733] Thu, 04 April 2024 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My advice would be to change "insertion_date" data type to TIMESTAMP WITH TIME ZONE and use SYSTIMESTAMP in the INSERT statement.
As per documentation:
Quote:
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.

You can then use on SELECT "AT TIME ZONE" to get the result in the time zone you want in the end.

Re: correct treatment of date across different timezones [message #689738 is a reply to message #689734] Thu, 04 April 2024 04:56 Go to previous messageGo to next message
jansulc
Messages: 4
Registered: April 2024
Junior Member
Oh, great.
Thank you. You replied greatly and so quickly like some 15 years ago, when I asked here something!
This time I even didn't get scolded by Black Swan:)

But anyway - i will use your solution. Thanks again for it.
Re: correct treatment of date across different timezones [message #689739 is a reply to message #689738] Thu, 04 April 2024 05:08 Go to previous messageGo to next message
jansulc
Messages: 4
Registered: April 2024
Junior Member
But anyway. One additional question.
Unfortunately I cannot test it now, therefore I ask:
Database A is in different timezone than database B.
Db A runs an insert:
insert into table_xyz@database_b values (systimestamp);

Do you say that the systimestamp will be the time of database B??
Because this is what I need.

Thanks,
Jan
Re: correct treatment of date across different timezones [message #689740 is a reply to message #689739] Thu, 04 April 2024 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, it'll give the timestamp at time zone of database A but as this data will contain the time zone when you'll query it you'll use:
select insertion_date at time zone dbtimezone from table_xyz;

If you really want the time of database B (in case the databases are not synchronized), you have to create in database B a function (say mytime) you will call from database A:
create or replace function mytime return timestamp with time zone
is
begin
  return systimestamp;
end;
/
select mytime@database_b from dual;
But this is useful ONLY if you can have different datetimes (both reduced to UTC), that is if
select systimestamp at time zone 'utc' from dual;
returns different results for the same moment.



Re: correct treatment of date across different timezones [message #689741 is a reply to message #689740] Thu, 04 April 2024 06:27 Go to previous message
jansulc
Messages: 4
Registered: April 2024
Junior Member
Understood.
Again - thank you!
Previous Topic: Implement Profile, PW Policy & Roles
Next Topic: Oracle ORA-00918: column ambiguously defined
Goto Forum:
  


Current Time: Thu May 23 10:06:39 CDT 2024