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 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