How to make Oracle EXTRACT(HOUR FROM CURRENT_TIMESTAMP) function return the actual hour (it returns one hour behind)

Question

I'm creating a trigger that must check if an action is taken inside an hour range in the day, say between 9 and 18(6pm).

The problem I am facing is when I use the Oracle EXTRACT() function it return a number which is 1 hour behind from actual hour.

Example,

select current_timestamp from dual;

gives me 22.09.01 12:49:23,733000000 EUROPE/LISBON as a result, but when I run:

select extract(HOUR FROM current_timestamp) from dual; 

I get 11 as a result, although it should be 12. I believe that this is due to daylight saving but I cannot solve it. I already tried to run:

ALTER SESSION SET TIME_ZONE='Europe/Lisbon';

but the problem remains. How can I retrieve the correct hour for my timezone?

Answer 1

Would TO_CHAR do?

This is what you have (actually, what I have):

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
01-SEP-22 02.03.51.592000 PM +02:00

SQL> select extract(hour from current_timestamp) from dual;

EXTRACT(HOURFROMCURRENT_TIMESTAMP)
----------------------------------
                                12

The TO_CHAR option:

SQL> select to_char(current_timestamp, 'hh24') from dual;

TO
--
14

SQL>