Let original_date be a date. How can I get back its value from the result of SYS_EXTRACT_UTC(cast(original_date as timestamp )), exclusively by internal Oracle functions, i.e. regardless of any timezone setting of the database.
Some stupid program writes the date values into one of the tables in UTC, that is
cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)
is stored instead of SYSDATE in a date type column of this table.
In all other tables simply SYSDATE is stored in such columns. My task is to use these values together, so I want to do back the effect of the SYS_EXTRACT_UTC() function. I can solve this only if I specify manually my time zone, i.e by
cast( FROM_TZ(cast(my_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Budapest' as date)
But if I use DBTIMEZONE instead of 'Europe/Budapest', then I get wrong result, probably because the summer daylight saving time is ignored by DBTIMEZONE
For example, when to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') = '2016-05-19 13:45:12', then program stores
cast(SYS_EXTRACT_UTC(cast(SYSDATE as timestamp)) as date)
My test query is:
SELECT
original_date,
stored_utc_date,
cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Budapest' as date) as reverted_good,
cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
and its result is:
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG
------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 11:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12
You could get the systimestamp time zone region and use that:
FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE to_char(systimestamp, 'TZR')
With your test data (but changing Budapest to London as that's my local region):
SELECT
original_date,
stored_utc_date,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE TO_CHAR(systimestamp, 'TZR') as date) as reverted_right
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
/
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG REVERTED_RIGHT
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12
Except... that doesn't work all the time, because the TZR is reported as an offset (since it's based on the operating system TZ), and you can't guess a region from an offset. If the original date was in winter and you run this in summer, or vice versa, then the reverted date will be an hour out. So effectively half the reverted dates will always be wrong - but which half will depend on when you run the query.
It looks like you can get around this by using DBTIMEZONE as a local time zone:
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE
as timestamp with local time zone
Your test query again:
SELECT
original_date,
stored_utc_date,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
cast(cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as timestamp with local time zone) as date) as reverted_right
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
/
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG REVERTED_RIGHT
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12
A wider test query with dates across the year:
with t as (
select from_tz(cast(add_months(trunc(sysdate, 'MM'), 1-level) as timestamp), 'Europe/London')
as original_systimestamp
from dual
connect by level <= 12
)
select original_systimestamp,
cast(cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as timestamp with local time zone) as date) as good_date,
sys_extract_utc(original_systimestamp) as utc_timestamp,
from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone to_char(systimestamp, 'TZR') as at_systimezone,
from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as at_dbtimezone,
cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as timestamp with local time zone) as at_local_dbtimezone
from t
order by original_systimestamp;
ORIGINAL_SYSTIMESTAMP GOOD_DATE UTC_TIMESTAMP AT_SYSTIMEZONE AT_DBTIMEZONE AT_LOCAL_DBTIMEZONE
----------------------------------- ------------------- --------------------- ---------------------------- ---------------------------- ----------------------------
2015-06-01 00:00:00.0 Europe/London 2015-06-01 00:00:00 2015-05-31 23:00:00.0 2015-06-01 00:00:00.0 +01:00 2015-05-31 23:00:00.0 +00:00 2015-06-01 00:00:00.0
2015-07-01 00:00:00.0 Europe/London 2015-07-01 00:00:00 2015-06-30 23:00:00.0 2015-07-01 00:00:00.0 +01:00 2015-06-30 23:00:00.0 +00:00 2015-07-01 00:00:00.0
2015-08-01 00:00:00.0 Europe/London 2015-08-01 00:00:00 2015-07-31 23:00:00.0 2015-08-01 00:00:00.0 +01:00 2015-07-31 23:00:00.0 +00:00 2015-08-01 00:00:00.0
2015-09-01 00:00:00.0 Europe/London 2015-09-01 00:00:00 2015-08-31 23:00:00.0 2015-09-01 00:00:00.0 +01:00 2015-08-31 23:00:00.0 +00:00 2015-09-01 00:00:00.0
2015-10-01 00:00:00.0 Europe/London 2015-10-01 00:00:00 2015-09-30 23:00:00.0 2015-10-01 00:00:00.0 +01:00 2015-09-30 23:00:00.0 +00:00 2015-10-01 00:00:00.0
2015-11-01 00:00:00.0 Europe/London 2015-11-01 00:00:00 2015-11-01 00:00:00.0 2015-11-01 01:00:00.0 +01:00 2015-11-01 00:00:00.0 +00:00 2015-11-01 00:00:00.0
2015-12-01 00:00:00.0 Europe/London 2015-12-01 00:00:00 2015-12-01 00:00:00.0 2015-12-01 01:00:00.0 +01:00 2015-12-01 00:00:00.0 +00:00 2015-12-01 00:00:00.0
2016-01-01 00:00:00.0 Europe/London 2016-01-01 00:00:00 2016-01-01 00:00:00.0 2016-01-01 01:00:00.0 +01:00 2016-01-01 00:00:00.0 +00:00 2016-01-01 00:00:00.0
2016-02-01 00:00:00.0 Europe/London 2016-02-01 00:00:00 2016-02-01 00:00:00.0 2016-02-01 01:00:00.0 +01:00 2016-02-01 00:00:00.0 +00:00 2016-02-01 00:00:00.0
2016-03-01 00:00:00.0 Europe/London 2016-03-01 00:00:00 2016-03-01 00:00:00.0 2016-03-01 01:00:00.0 +01:00 2016-03-01 00:00:00.0 +00:00 2016-03-01 00:00:00.0
2016-04-01 00:00:00.0 Europe/London 2016-04-01 00:00:00 2016-03-31 23:00:00.0 2016-04-01 00:00:00.0 +01:00 2016-03-31 23:00:00.0 +00:00 2016-04-01 00:00:00.0
2016-05-01 00:00:00.0 Europe/London 2016-05-01 00:00:00 2016-04-30 23:00:00.0 2016-05-01 00:00:00.0 +01:00 2016-04-30 23:00:00.0 +00:00 2016-05-01 00:00:00.0
But even that only works if the session time zone matches the database server's region; if I set the session time zone to something other then Europe/London it's way off. And it you're relying on being able to set the session time zone, the first query in the question with the region hard-coded isn't really much worse...
It's also worth noting that DBTIMEZONE doesn't necessarily tell you anything useful; Oracle recommend setting it to UTC. So if you can't use that, and can't really use the TZR extracted from systimestamp (since it's actually an offset, and you can't convert that to a region so can't know DST info), I don't think there's a way to figure out the original system time from anything held in the database. It seems that you need to supply the server time zone region at some point - either by setting the session time zone, or as in your original query.