CODEWITHSUNDEEP
sqloracle-database
Priit Mets
Priit Mets

Reputation: 495

Why I get different answers while getting hours from a timestamp?

I have following code

Select 
EXTRACT(HOUR FROM (FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki')) as hour,
TO_CHAR(FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki','HH24') as hour2
from dual

Output is:

HOUR    HOUR2
12       15

This function sets time to UTC and then converts to Europe/Helsinki and then gets an hour from the timestamp. Why do I have different answers here?

Upvotes: 0

Views: 211

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

From the documentation:

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

You are passing in the converted value, which is going to evaluate to something like 2021-06-04 15:14:13 EUROPE/HELSINKI; when that is implicitly converted back to UTC it becomes 2021-06-04 12:14:13 UTC, so extract returns 12.

Your conversion seems to be overly complicated, and wrong; it shouldn't be giving you 15 at the time you posted, and the 12 was correct (I think, as you posted at 09:38 UTC) but only because your server happens to be in +03:00 time zone as well (I think, again, from the values you saw).

If you want the current time in Helsinki then you you can simply use SYSTIMESTAMP at time zone 'Europe/Helsinki'.

To pass that into extract() without it being treated as UTC, you can cast to a plain timestamp at that point:

EXTRACT(HOUR FROM CAST(SYSTIMESTAMP at time zone 'Europe/Helsinki' AS TIMESTAMP)) as hour

which gives the correct answer of 13 at the moment.

db<>fiddle (server in UK time zone, so BST)

Upvotes: 3

Related Questions