Product Documentation

Appearance

TO_TIMESTAMP ​

Return a TIMESTAMPTZ value from either a character string or a UNIX epoch value.

TO_TIMESTAMP(string, format)
TO_TIMESTAMP(epoch)

When an input string is the first argument, its format is the second argument. You must specify the format of the input string. See Formats for Datetime Values.

For example:

premdb=# SELECT TO_TIMESTAMP('May 30 2016','Mon DD YYYY');
     to_timestamp      
------------------------
 2016-05-30 00:00:00-07
(1 row)

The following example shows how to use this function with an epoch value (number of seconds since January 1st, 1970):

premdb=# SELECT TO_TIMESTAMP(1464700000) FROM sys.const;
     to_timestamp      
------------------------
 2016-05-31 06:06:40-07
(1 row)

The following example converts a string to a TIMESTAMPTZ value, then inserts it into a TIMESTAMP column:

premdb=# INSERT INTO match(matchday) SELECT TO_TIMESTAMP('May 31 2016','Mon DD YYYY');
INSERT 0 1
premdb=# SELECT * FROM match WHERE matchday='2016-05-31';
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
         | 2016-05-31 00:00:00 |      |      |         | 
(1 row)

Note: You cannot repeat format elements. For example, because the function repeats SS, the following query returns an error:

premdb=# select to_timestamp('2018 02 01 12 15 15', 'YYYY MM DD HH MI SS SS') from sys.const;
ERROR:  Invalid datetime format YYYY MM DD HH MI SS SS
DETAIL:   [reason => Invalid datetime format string. Reason: Duplicate token SS found in format string. Duplicate tokens are not allowed.]

Parent topic:Formatting Functions