CODEWITHSUNDEEP
sqloracle-database
Priit Mets
Priit Mets

Reputation: 495

How change time zone in SQL for a timestamp?

I have a time variable in a timestamp format (i.e. '25.04.2021 09:00:00') I found an only way to transform this timestamp in UTC to Helsinki time using this code

select FROM_TZ( datetime, 'UTC' ) AT TIME ZONE 'EUROPE/Helsinki' AS hel_time
from MYDB

However, I got an error. Can you help with the transformation?

Upvotes: 0

Views: 1423

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

If datetime is an Oracle DATE type then you cast that to a timestamp:

FROM_TZ(CAST(datetime AS TIMESTAMP), 'UTC')

giving you:

select FROM_TZ(CAST(datetime AS TIMESTAMP), 'UTC' ) AT TIME ZONE 'EUROPE/Helsinki' AS hel_time
from MYDB

25-APR-21 12.00.00.000000 EUROPE/HELSINKI

db<>fiddle

it isn't clear where Java fits in, or where you have a 'time' variable.

Upvotes: 1

Related Questions