Skip to Main Content
  • Questions

  • How does one convert a column of values of different timezone to a common timezone?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ankit.

Asked: January 24, 2024 - 6:51 am UTC

Last updated: January 25, 2024 - 2:50 pm UTC

Version: 20.2.0

Viewed 1000+ times

You Asked

I have a column with different timezone data. I need to convert every row to pst.


----------------------
| COLUMN |
----------------------
|01/17/2024 18:00 PST|
|01/16/2024 18:00 CST|
|01/18/2024 12:00 IST|
|01/18/2024 07:00 -05|
|01/16/2024 14:00 -05|
|01/17/2024 18:00 IST|
|01/18/2024 17:00 IST|
|01/17/2024 16:00 GMT|
|01/18/2024 14:00 EST|
|01/17/2024 16:00 -05|
----------------------


and Chris said...

You can use the AT TIME ZONE clause to convert them:

select 
  to_timestamp_tz ( '01/17/2024 18:00 PST', 'MM/DD/YYYY HH24:MI TZR' ) at time zone 'CET',
  to_timestamp_tz ( '01/17/2024 16:00 GMT', 'MM/DD/YYYY HH24:MI TZD' ) at time zone 'CET'
from dual;

TO_TIMESTAMP_TZ('01/17/202418:00P TO_TIMESTAMP_TZ('01/17/202416:00G
--------------------------------- ---------------------------------
18/01/2024 03:00:00.000000000 CET 17/01/2024 17:00:00.000000000 CET


Ensure that the values are TIMESTAMP WITH TIME ZONEs or you'll get implicit conversions which may lead to unexpected results.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.