NEW_TIME

Converts a timestamp value from one time zone to another and returns a TIMESTAMP.

Behavior Type

Immutable

Syntax

NEW_TIME( 'timestamp' , 'timezone1' , 'timezone2')

Parameters

timestamp

The timestamp to convert, conforms to one of the following formats:
timezone1
timezone2

Specify the source and target timezones, one of the strings defined in /opt/vertica/share/timezonesets. For example:

  • GMT: Greenwich Mean Time
  • AST / ADT: Atlantic Standard/Daylight Time
  • EST / EDT: Eastern Standard/Daylight Time
  • CST / CDT: Central Standard/Daylight Time
  • MST / MDT: Mountain Standard/Daylight Time
  • PST / PDT: Pacific Standard/Daylight Time

Examples

Convert the specified time from Eastern Standard Time (EST) to Pacific Standard Time (PST):

=> SELECT NEW_TIME('05-24-12 13:48:00', 'EST', 'PST');
      NEW_TIME
---------------------
 2012-05-24 10:48:00
(1 row)

Convert 1:00 AM January 2012 from EST to PST:

=> SELECT NEW_TIME('01-01-12 01:00:00', 'EST', 'PST');
      NEW_TIME
---------------------
 2011-12-31 22:00:00
(1 row)

Convert the current time EST to PST:

=> SELECT NOW();
              NOW
-------------------------------
 2016-12-09 10:30:36.727307-05
(1 row)

=> SELECT NEW_TIME('NOW', 'EDT', 'CDT');
          NEW_TIME
----------------------------
 2016-12-09 09:30:36.727307
(1 row)

The following example returns the year 45 before the Common Era in Greenwich Mean Time and converts it to Newfoundland Standard Time:

=>  SELECT NEW_TIME('April 1, 45 BC', 'GMT', 'NST')::DATE;
   NEW_TIME
---------------
 0045-03-31 BC
(1 row)