Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_convert_a_timestamp_to_different_timezones.sql

Last active January 1, 2018 16:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ateneva/77b6d5dfd21906d34b4a071cc1ddd1fe to your computer and use it in GitHub Desktop.
Save ateneva/77b6d5dfd21906d34b4a071cc1ddd1fe to your computer and use it in GitHub Desktop.
Download ZIP
How to convert a timestamp to different zones
Raw
SQL_convert_a_timestamp_to_different_timezones.sql
--------------------------------Vertica------------------------------------------------------------------
select
getdate() as ServerTime,
timestampadd('hour', -1, getdate()) as Chicago,
timestampadd('hour', -3, getdate()) as LA,
timestampadd('hour', 5, getdate()) as GMT,
timestampadd('hour', 6, getdate()) as CET,
timestampadd('hour', 7, getdate()) as EET
Chicago |LA |GMT |CET |EET |
--------------------|--------------------|--------------------|--------------------|--------------------|
2017-10-23 12:39:04 |2017-10-23 10:39:04 |2017-10-23 18:39:04 |2017-10-23 19:39:04 |2017-10-23 20:39:04 |
--------------------------------SQL Server---------------------------------------------------------------
select
dateadd(hour, -1, getdate()) as Chicago,
dateadd(hour, -3, getdate()) as LA,
dateadd(hour, 5, getdate()) as GMT,
dateadd(hour, 6, getdate()) as CET,
dateadd(hour, 7, getdate()) as EET
Chicago |LA |GMT |CET |EET |
--------------------|--------------------|--------------------|--------------------|--------------------|
2017-10-23 12:39:04 |2017-10-23 10:39:04 |2017-10-23 18:39:04 |2017-10-23 19:39:04 |2017-10-23 20:39:04 |
---------------------------------MySQL---------------------------------------------------------------------
select
date_add(Now(), interval -1 hour) as Chicago,
date_add(Now(), interval -3 hour) as LA,
date_add(Now(), interval 5 hour) as GMT,
date_add(Now(), interval 6 hour) as CET,
date_add(Now(), interval 7 hour) as EET
Chicago |LA |GMT |CET |EET |
--------------------|--------------------|--------------------|--------------------|--------------------|
2017-10-23 12:39:04 |2017-10-23 10:39:04 |2017-10-23 18:39:04 |2017-10-23 19:39:04 |2017-10-23 20:39:04 |
--------------------------------PostgreSQL------------------------------------------------------------------
select
Now() - INTERVAL '1 hour' as Chicago,
Now() - INTERVAL '3 hour' as LA,
Now() + INTERVAL '5 hour' as GMT,
Now() + INTERVAL '6 hour' as CET,
Now() + INTERVAL '7 hour' as EET
Chicago |LA |GMT |CET |EET |
--------------------|--------------------|--------------------|--------------------|--------------------|
2017-10-23 12:39:04 |2017-10-23 10:39:04 |2017-10-23 18:39:04 |2017-10-23 19:39:04 |2017-10-23 20:39:04 |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment