Hi all,
I have a table with timestamps (DATE type) in local German time which I'm trying to convert to UTC. The query below returns some data from around the last switch to daylight saving time on March 30th 2014 (clock was advanced one hour at 2 am to 3 am, or from UTC+1 to UTC+2).
SELECT BEGIN_TIME, sys_extract_utc(FROM_TZ(CAST(BEGIN_TIME AS TIMESTAMP), 'Europe/Berlin')) AS BEGIN_TIME_UTC from ora_mon_prod.data_8w
WHERE BEGIN_TIME >= TO_DATE('2014-03-30 00:00', 'YYYY-MM-DD HH24:MI')
AND BEGIN_TIME <= TO_DATE('2014-03-30 06:00', 'YYYY-MM-DD HH24:MI')
ORDER BY BEGIN_TIME
| BEGIN_TIME | BEGIN_TIME_UTC | |
------------------- -------------------
2014-03-30 00:00:00 2014-03-29 23:00:00 => standard time, UTC+1 => OK
2014-03-30 01:00:00 2014-03-30 00:00:00 => standard time, UTC+1 => OK
2014-03-30 03:00:00 2014-03-30 01:00:00 => switch to DST, UTC+2 => OK
2014-03-30 04:00:00 2014-03-30 03:00:00 => this should be 02:00:00 in UTC!
2014-03-30 05:00:00 2014-03-30 03:00:00 => DST, UTC+2 => OK
2014-03-30 06:00:00 2014-03-30 04:00:00 => DST, UTC+2 => OK
Can someone explain why for 4 AM local German time (daylight saving time, UTC+2), sys_extract_utc returns 3 AM? Am I missing something or is this a bug?
Btw, this does not seem to be an issue with my data, as I can reproduce this result even with this simple line:
select sys_extract_utc(TIMESTAMP '2014-03-30 04:00:00 Europe/Berlin') AS UTC FROM DUAL
-------------------
2014-03-30 03:00:00
I'm running this query using DbVisualizer against a Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.
Thanks in advance.