This is an old revision of the document!
- Wrong function extract
Very interesting “Feature” which I was shown in oracle is the function “extract hour from timestamp”, it appears that Oracle will provide an hour which is wrong:
select (extract (hour from current_timestamp)),current_timestamp from dual; (EXTRACT(HOURFROMCURRENT_TIMESTAMP)) CURRENT_TIMESTAMP ------------------------------------ ------------------- 9 2017-11-14 10:44:00
Solution This behavior is somehow normal however, since the function: extract (hour from current_timestamp) is supposed to return you the hour in UTC format, excluding the timezone, therefore if the hour is 18:00 and the timezone is +02:00 it will return you 16:00, instead of 18:00. There are 2 ways how to negate this:
- Use the current_scn:
SQL> select (extract (hour from (scn_to_timestamp(current_scn)))) from v$database;
(EXTRACT(HOURFROM(SCN_TO_TIMESTAMP(CURRENT_SCN))))
--------------------------------------------------
17
SQL>
- Use the sum of the UPC hour and the time zone:
SQL> Select (extract (HOUR from current_timestamp)), current_timestamp from dual; -------- We will extract the UTC value for hour
SQL> Select (extract (HOUR from current_timestamp)) UTC_HOUR, (extract (TIMEZONE_HOUR from current_timestamp)) SESSION_TZ, (extract (HOUR from current_timestamp))+(extract (TIMEZONE_HOUR from current_timestamp)) SESSION_HOUR ,current_timestamp from dual;
UTC_HOUR SESSION_TZ SESSION_HOUR CURRENT_TIMESTAMP
---------- ---------- ------------ ---------------------------------------------------------------------------
16 1 17 16-NOV-17 17.26.55.236687 +01:00