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
or
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
prefixfill mode (suppress leading zeroes and padding blanks) TH
suffixupper case ordinal number suffix th
suffixlower case ordinal number suffix FX
prefixfixed format global option (see usage notes) TM
prefixtranslation mode (use localized day and month names based on lc_time) SP
suffixspell 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