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?

CodePudding user response：

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>

CodePudding user response：

From the documentation:

When extracting from a datetime with a time zone value, the value returned is in UTC.

You can explicitly cast to a plain timestamp first to avoid that by discarding the time zone information:

select extract(HOUR FROM cast(current_timestamp as timestamp)) from dual;

You can see the difference with:

select current_timestamp,
  extract(HOUR FROM current_timestamp) as utc,
  extract(HOUR FROM cast(current_timestamp as timestamp)) as result
from dual;

CURRENT_TIMESTAMP                          UTC RESULT
--------------------------------------- ------ ------
01-SEP-22 14.11.54.505920 EUROPE/LISBON     13     14

db<>fiddle

check if an action is taken inside an hour range in the day

If you base this check on current_timestamp then a user would potentially be able to work around it by changing their session time zone, to make it appear that 'now' is in the allowed window, whatever the time really is. If you use systimestamp instead then they wouldn't be able to do that. (You may need to adjust the window if the system is in a different time zone - check if systimestamp is reporting your local Lisbon time.)

CodePudding user response：

I have to deal with this from time to time working for a global company.

Like you mentioned check your timestamp.

SELECT SESSIONTIMEZONE FROM DUAL;

Then try to put you region in this function to determine the delta

SELECT TZ_OFFSET('Europe/London') FROM DUAL;  --Put your region here

This could be used to force an extra hour if you already know the difference.

select extract(HOUR FROM current_timestamp   INTERVAL '1' HOUR) FROM DUAL;

You could also try to use the FROM_TZ (Timezone) function to add or subtract time from your date/time.

SELECT FROM_TZ(TIMESTAMP '2022-09-01 12:44:00', ' 01:00') FROM DUAL;