We can select various pseudocolumns that deal with time.
- sysdate is the local time (date() in unix)
- systimestamp returns the time including timezone offset compared to the coordinated universal time (UTC).
- dbtimezone returns just the offset.
Example
select sysdate as ti_sysdate
, systimestamp ti_systimestamp
, dbtimezone ti_tzoffset
from dual
TI_SYSDATE TI_SYSTIMESTAMP TI_TZOFFSET
23.04.15 14:40:55 23.04.15 14:40:55,133547000 +02:00 +02:00
This is a database near Berlin. Berlin has +1 hour offset to UTC and additionally +1 hour for summer time.
If we access a remote database and want to know about the remote time, it becomes a little difficult.
One way would be to have a remote plsql function that returns us such a time.
But this is not necessary and not always are we allowed to create remote functions.
Assuming we have a link to a remote database called “remote_database”.
select sysdate as ti_sysdate
, systimestamp ti_systimestamp
, dbtimezone ti_tzoffset
from dual@remote_database
TI_SYSDATE TI_SYSTIMESTAMP TI_TZOFFSET 23.04.15 14:41:04 23.04.15 08:41:04,065243000 -04:00 -04:00
In this case the database is located near Boston. And that is where the -4 hour offset comes from.
So between the two databases we have a total time difference of 6 hours.
Quirk 1 – sysdate vs. systimestamp
The problem is that SYSDATE always returns the local (Berlin) time = 23.04.15 14:41:04
The good thing is that systimestamp and dbtimezone return the values from the remote db.
It is a little suprising thou, that sysdate and systimestamp work differently in the scope of remote queries.
Quirk 2 – distributed queries
So lets try to get all the times with one select.
with remote_tz as (select sysdate as ti_sysdate, systimestamp ti_systimestamp, dbtimezone ti_tzoffset from dual@remote_database)
,local_tz as (select sysdate as ti_sysdate, systimestamp ti_systimestamp, dbtimezone ti_tzoffset from dual)
select 'REMOTE' as src, r.* from remote_tz r
union all
select 'LOCAL' as src, l.* from local_tz l;
SRC TI_SYSDATE TI_SYSTIMESTAMP TI_TZOFFSET REMOTE 23.04.15 14:51:03 23.04.15 14:51:03,063763000 +02:00 +02:00 LOCAL 23.04.15 14:51:03 23.04.15 14:51:03,063763000 +02:00 +02:00
Ouch! This didn’t work.
Why? This is a distributed query. The statement is executed on one site, default is on the local database. The remote data is fetched from the remote site and moved to the local site. The full statement then is executed locally. During this step the systimestamp pseudocolumn is calculated.
If we add a driving site hint the result changes. The query is then executed remotely including (some) pseudocolumns.
with remote_tz as (select sysdate as ti_sysdate, systimestamp ti_systimestamp, dbtimezone ti_tzoffset from dual@remote_database) ,local_tz as (select sysdate as ti_sysdate, systimestamp ti_systimestamp, dbtimezone ti_tzoffset from dual) select /*+driving_site (r) */ 'REMOTE' as src, r.* from remote_tz r union all select 'LOCAL' as src, l.* from local_tz l;
SRC TI_SYSDATE TI_SYSTIMESTAMP TI_TZOFFSET REMOTE 23.04.15 14:54:49 23.04.15 08:54:49,565199000 -04:00 -04:00 LOCAL 23.04.15 14:54:49 23.04.15 08:54:49,565199000 -04:00 -04:00
Quirk 3 – materialization
with remote_tz as (select /*+materialize */ sysdate as ti_sysdate, systimestamp ti_systimestamp, dbtimezone ti_tzoffset from dual@remote_database) select * from remote_tz;
TI_SYSDATE TI_SYSTIMESTAMP TI_TZOFFSET
23.04.15 14:51:03 23.04.15 14:51:03,063763000 +02:00 +02:00
In the scope of the previous findings this makes a little sense. The data is materialized locally. Therefore the whole query is executed locally. Systimestamp is fetched from the local database. Although a query for a single table would execute on the remote database without this hint.
Fazit
Be careful with sysdate and systimestamp in combination with database links.
Remember: In very rare cases are hints able to change the results of a query!
svenweller 