PostgreSQL to_timestamp() Function

The PostgreSQL to_timestamp() function converts Unix epoch time to a standard timestamp with time zone information, or convert the specified string to timestamp according to the given format.

`to_timestamp()` Syntax

PostgreSQL to_timestamp ()functions have two syntactic forms:

to_timestamp(unix_epoch DOUBLE) -> TIMESTAMP WITH TIME ZONE

to_timestamp(str TEXT, format TEXT) -> TIMESTAMP WITH TIME ZONE

Parameters

unix_epoch

Required. The Unix epoch time (seconds since 1970-01-01 00:00:00+0).

str

Required. The string to convert≈.

format

Required. The format in which to convert this time string. You can use the template patterns in the following table to construct format strings.

Pattern	Description
`HH`	Hour of the day (01–12)
`HH12`	Hour of the day (01–12)
`HH24`	Hour of the day (00–23)
`MI`	Minutes (00–59)
`SS`	seconds (00–59)
`MS`	milliseconds (000–999)
`US`	Microseconds (000000–999999)
`FF1`	tenths of a second (0–9)
`FF2`	hundredths of a second (00–99)
`FF3`	milliseconds (000–999)
`FF4`	Tenth of a millisecond (0000–9999)
`FF5`	hundredths of a millisecond (00000–99999)
`FF6`	Microseconds (000000–999999)
`SSSS`,`SSSSS`	Seconds after midnight (0–86399)
`AM`, `am`, `PM`,`pm`	AM, PM (no period)
`A.M.`, `a.m.`, `P.M.`,`p.m.`	AM, PM (with periods)
`Y,YYY`	Year (4 or more digits) with commas
`YYYY`	year (4 or more digits)
`YYY`	Last 3 digits of year
`YY`	Last 2 digits of year
`Y`	last digit of year
`IYYY`	ISO 8601 week number year (4 or more digits)
`IYY`	Last 3 digits of ISO 8601 week number year
`IY`	Last 2 digits of ISO 8601 week number year
`I`	ISO 8601 week number last digit of year
`BC`, `bc`, `AD`,`ad`	BC, AD (no period)
`B.C.`, `b.c.`, `A.D.`,`a.d.`	BC, AD (with periods)
`MONTH`	All-caps month name (blank padded to 9 characters)
`Month`	All-caps month name (blank padded to 9 characters)
`month`	All lowercase month name (blank padded to 9 characters)
`MON`	Abbreviated uppercase month name (3 characters in English, localized lengths vary)
`Mon`	Abbreviated uppercase month name (3 characters in English, localized lengths vary)
`mon`	Abbreviated lowercase month name (3 characters in English, localized lengths vary)
`MM`	Number of months (01–12)
`DAY`	Date name in all caps (blank padded to 9 characters)
`Day`	Date name in all caps (blank padded to 9 characters)
`day`	All lowercase date name (9 characters padded with blanks)
`DY`	Abbreviated uppercase date name (3 English characters, localized lengths vary)
`Dy`	Abbreviated uppercase date name (3 English characters, localized lengths vary)
`dy`	Abbreviated lowercase date name (3 English characters, localized lengths vary)
`DDD`	day of the year (001–366)
`IDDD`	ISO 8601 week number day of the year (001–371; the 1st day of the year is the Monday of the first ISO week)
`DD`	day of the month (01–31)
`D`	Day of the week, Sunday ( `1`) to Saturday ( `7`)
`ID`	ISO 8601 Day of the week, Monday ( `1`) to Sunday ( `7`)
`W`	Week of the month (1–5) (the first week starts on the first day of the month)
`WW`	Week number of the year (1–53) (the first week starts on the first day of the year)
`IW`	ISO 8601 week number Week number of the year (01–53; the first Thursday of the year is in week 1)
`CC`	Century (2 digits) (21st century started on 2001-01-01)
`J`	Julian calendar date (integer days since local midnight on November 24, 4714 BC)
`Q`	quarter
`RM`	Month in uppercase Roman numerals (I to XII; I is January)
`rm`	Month in lowercase Roman numerals (i to xii; i is January)
`TZH`	time zone hours
`TZM`	time zone minutes

You can use the modifiers in the following table with date/time template patterns.

Modifier	Description
`FM` prefix	fill mode (suppress leading zeroes and padding blanks)
`TH` suffix	upper case ordinal number suffix
`th` suffix	lower case ordinal number suffix
`FX` prefix	fixed format global option (see usage notes)
`TM` prefix	translation mode (use localized day and month names based on lc_time)
`SP` suffix	spell mode (not implemented)

Return value

The PostgreSQL to_timestamp() function returns a timestamp value with time zone information converted from a string according to the specified format.

`to_timestamp()` Examples

Convert Unix epoch time

This example shows how to use the PostgreSQL to_timestamp() function to convert a Unix epoch time to a standard timestamp with time zone information and return it.

SELECT to_timestamp(1652704873);

      to_timestamp
------------------------
 2022-05-16 20:41:13+08

Convert time string

This example shows how to use the PostgreSQL to_timestamp() function to convert a specified string to a timestamp value according to the specified format.

Example 1

SELECT to_timestamp('2022-05-17', 'YYYY-MM-DD');

      to_timestamp
------------------------
 2022-05-17 00:00:00+08

Example 2

SELECT to_timestamp('2022-05-17 10:10:10.022.000001', 'YYYY-MM-DD HH:MI:SS.MS.US');

         to_timestamp
-------------------------------
 2022-05-17 10:10:10.022001+08

to_timestamp() Syntax