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)
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:
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
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)