Oracle SQL : how to specify Time Zone Region-CodePudding

to_date('30/03/2022', 'DD/MM/YYYY')

Underlined, as hours are not specified, that means that hour is '00:00' I would like to specify that this is for Europe/Paris time zone region. Can you help me set-up this ? Thanks

CodePudding user response：

A DATE data type has the components: year, month, day, hour, minute and second. It ALWAYS has those components and NEVER stores anything else (such as a time zone); so it is impossible to store a time zone in a DATE data type.

A TIMESTAMP data type has the components: year, month, day, hour, minute and second and, optionally, can store fractional seconds.

A TIMESTAMP WITH TIME ZONE data type has the components: year, month, day, hour, minute, second and time zone and, optionally, can store fractional seconds information.

Therefore, if you want to store a time zone then you should use TIMESTAMP WITH TIME ZONE and not DATE.

Your code would then be:

TO_TIMESTAMP_TZ('30/03/2022 Europe/Paris', 'DD/MM/YYYY TZR')

or using a timestamp literal:

TIMESTAMP '2022-03-30 00:00:00 Europe/Paris'

or, if you want to pass in your date in that format and add the time zone in a two-step process:

FROM_TZ(TO_TIMESTAMP('30/03/2022', 'DD/MM/YYYY'), 'Europe/Paris')

db<>fiddle here