Traveling Across Time Zones

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Traveling Across Time Zones

Post by JimKnicely » Thu Mar 14, 2013 12:52 pm

A few days ago an inquisitive co-worker asked me how to convert a UTC (Coordinated Universal Time) date/time into a standard local date/time in SQL Server. Shockingly I didn’t know the answer right away :oops: but we were able to figure it out together.

Here are few examples showing how to convert from the UTC time zone into the EDT (Easter Daylight Time) time zone in a few other databases, included our beloved Vertica!

Keep in mind that you can follow these examples to figure out on your own how to convert from any time zone into any other time zone!

MySQL:

We can use the CONVERT_TZ function to translate between time zones:

Code: Select all

MySQL> SELECT current_timestamp "Current local date and time",
    ->        utc_timestamp()   "Current UTC date and time",
    ->        convert_tz(utc_timestamp(), 'Etc/UTC', 'EDT') "Convert UTC to EDT";
+-----------------------------+---------------------------+---------------------+
| Current local date and time | Current UTC date and time | Convert UTC to EDT  |
+-----------------------------+---------------------------+---------------------+
| 2012-10-05 11:02:14         | 2012-10-05 15:02:14       | 2012-10-05 11:02:14 |
+-----------------------------+---------------------------+---------------------+
1 row in set (0.00 sec)
SQL Server:

Although there is not a specific built in function to convert between times zone, with a little ingenuity it can be done using a combination of the DATEADD and DATEDIFF functions:

Image

Oracle:

Oracle gives us the NEW_TIME function:

Code: Select all

SQL> SELECT systimestamp "Current local date and time",
  2         sys_extract_utc(systimestamp) "Current UTC date and time",
  3         to_char(new_time(sys_extract_utc(systimestamp), 'GMT', 'EDT'), 'YYYY-MM-DD HH:MI:SS AM') "Convert UTC to EDT"
  4    FROM dual;

Current local date and time           Current UTC date and time     Convert UTC to EDT
-----------------------------------   ----------------------------  ---------------------- 
05-OCT-12 11.30.27.688000 AM -04:00   05-OCT-12 03.30.27.688000 PM  2012-10-05 11:30:27 AM
Vertica:

Starting in Vertica 2.5, many of the Oracle’s built-in functions were duplicated in Vertica. This included the NEW_TIME function:

Code: Select all

dbadmin=> SELECT getdate() "Current local date and time",
dbadmin->        getutcdate() "Current UTC date and time",
dbadmin->        new_time(getutcdate(), 'UTC', 'EDT') "Convert UTC to EDT";
Current local date and time | Current UTC date and time  |     Convert UTC to EDT
-----------------------------+----------------------------+----------------------------
2012-10-05 11:45:51.569381  | 2012-10-05 15:45:51.569381 | 2012-10-05 11:45:51.569381
(1 row)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”