-1
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

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • 1
    Dates don't hold time zone information; you would need a `timestamp with time zone` value. What are you doing with this value - inserting it into a table? If so what data type if the column? And is the string part being supplied, or is it a fixed value - so you could use a timestamp literal, e.g. `timestamp '2022-03-30 00:00:00 Europe/Paris'`? – Alex Poole Feb 16 '22 at 10:37

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117