Using Time Zones With Vertica

Vertica uses the public-domain Time Zone Database, also known as the tz Database.

Vertica uses the TZ environment variable on each node, if it has been set, for the default current time zone. Otherwise, Vertica uses the operating system time zone.

The TZ variable can be set by the operating system during login (see /etc/profile, /etc/profile.d, or /etc/bashrc) or by the user in .profile, .bashrc or .bash-profile

TZ must be set to the same value on each node when you start Vertica.

The following command returns the current time zone for your database:

=> SHOW TIMEZONE;
   name   |     setting    
----------+------------------
 timezone | America/New_York
(1 row)

You can also use the SET TIMEZONE TO { value | 'value' } command to set the time zone for a single session. 

There is no database default time zone; instead, TIMESTAMP WITH TIMEZONE (TIMESTAMPTZ) data is stored in GMT (UTC) by converting data from the current local time zone to GMT. 

When TIMESTAMPTZ data is used, data is converted back to use the current local time zone, which might be different from the local time zone where the data was stored. This conversion takes into account Daylight Saving Time (Summer Time), if applicable, depending on the year and date, to know when the Daylight Saving Time change occurred.

TIMESTAMP WITHOUT TIMEZONE data stores the timestamp, as given, and retrieves it exactly as given. The current time zone is ignored.  The same is true for TIME WITHOUT TIMEZONE. For TIME WITH TIMEZONE (TIMETZ), however, the current time zone setting is stored along with the given time, and that time zone is used on retrieval. 

Vertica recommends that you use TIMESTAMPTZ, not TIMETZ.

TIMESTAMPTZ uses the current time zone on both input and output, such as in the following example:

=> CREATE TEMP TABLE s (tstz TIMESTAMPTZ);=> SET TIMEZONE TO 'America/New_York';
=> INSERT INTO s VALUES ('2009-02-01 00:00:00');
=> INSERT INTO s VALUES ('2009-05-12 12:00:00');
=> SELECT tstz AS 'Local timezone', tstz AT TIMEZONE 'America/New_York' AS 'America/New_York', 
   tstz AT TIMEZONE 'GMT' AS 'GMT' FROM s;
     Local timezone     |  America/New_York   |         GMT        
------------------------+---------------------+---------------------
 2009-02-01 00:00:00-05 | 2009-02-01 00:00:00 | 2009-02-01 05:00:00
 2009-05-12 12:00:00-04 | 2009-05-12 12:00:00 | 2009-05-12 16:00:00
(2 rows)

The -05 in the Local time zone column above shows that the data is displayed in EST, while -04 indicates EDT.  The other two columns show the TIMESTAMP WITHOUT TIMEZONE at the specified time zone.

The next example illustrates what occurs if the current time zone is changed to, for example, Greenwich Mean Time:

=> SET TIMEZONE TO 'GMT';=> SELECT tstz AS 'Local timezone', tstz AT TIMEZONE 'America/New_York' AS 
   'America/New_York', tstz AT TIMEZONE 'GMT' as 'GMT' FROM s;
     Local timezone     |  America/New_York   |         GMT        
------------------------+---------------------+---------------------
 2009-02-01 05:00:00+00 | 2009-02-01 00:00:00 | 2009-02-01 05:00:00
 2009-05-12 16:00:00+00 | 2009-05-12 12:00:00 | 2009-05-12 16:00:00
(2 rows)

The +00 in the Local time zone column above indicates that TIMESTAMPTZ is displayed in 'GMT'.

The approach of using TIMESTAMPTZ fields to record events captures the GMT of the event, as expressed in terms of the local time zone.  Later, it allows for easy conversion to any other time zone, either by setting the local time zone or by specifying an explicit AT TIMEZONE clause.

The following example shows how TIMESTAMP WITHOUT TIMEZONE fields work in Vertica.

=> CREATE TEMP TABLE tnoz (ts TIMESTAMP);=> INSERT INTO tnoz VALUES('2009-02-01 00:00:00');
=> INSERT INTO tnoz VALUES('2009-05-12 12:00:00');
=> SET TIMEZONE TO 'GMT';
=> SELECT ts AS 'No timezone', ts AT TIMEZONE 'America/New_York' AS 
   'America/New_York', ts AT TIMEZONE 'GMT' AS 'GMT' FROM tnoz;
      No timezone    |    America/New_York    |          GMT         
---------------------+------------------------+------------------------
 2009-02-01 00:00:00 | 2009-02-01 05:00:00+00 | 2009-02-01 00:00:00+00
 2009-05-12 12:00:00 | 2009-05-12 16:00:00+00 | 2009-05-12 12:00:00+00
(2 rows)

The +00 at the end of a timestamp indicates that the setting is TIMESTAMP WITH TIMEZONE in GMT (the current time zone). The 'America/New_York' column shows what the 'GMT' setting was when you recorded the time, assuming you read a normal clock in the time zone 'America/New_York'. What this shows is that if it is midnight in the 'America/New_York' time zone, then it is 5 am GMT. 

00:00:00 Sunday February 1, 2009 in America/New_York converts to 05:00:00 Sunday February 1, 2009 in GMT.

The 'GMT' column displays the GMT time, assuming the input data was captured in GMT.

If you don't set the time zone to GMT, and you use another time zone, for example 'America/New_York', then the results display in 'America/New_York' with a -05 and -04, showing the difference between that time zone and GMT.

=> SET TIMEZONE TO 'America/New_York';
=> SHOW TIMEZONE;
    name   |     setting    
 ----------+------------------
  timezone | America/New_York
 (1 row)
=> SELECT ts AS 'No timezone', ts AT TIMEZONE 'America/New_York' AS 
   'America/New_York', ts AT TIMEZONE 'GMT' AS 'GMT' FROM tnoz;
      No timezone    |    America/New_York    |          GMT         
---------------------+------------------------+------------------------
 2009-02-01 00:00:00 | 2009-02-01 00:00:00-05 | 2009-01-31 19:00:00-05
 2009-05-12 12:00:00 | 2009-05-12 12:00:00-04 | 2009-05-12 08:00:00-04
(2 rows)

In this case, the last column is interesting in that it returns the time in New York, given that the data was captured in 'GMT'.