SQL Pocket Guide, 3rd Edition

Datetime Conversions: PostgreSQL

Convert between datetimes and character strings using the following functions:

TO_CHAR({timestamp|interval}, format)
TO_DATE(string, format)
TO_TIMESTAMP(string, format)

For example, to convert a date to the character representation of a timestamp, specify:

SELECT u.name,
       TO_CHAR(u.confirmed_date, 'dd-Mon-YYYY')
FROM upfall u;

      name       |   to_char
-----------------+-------------
 Munising Falls  | 08-Dec-2005
 Tannery Falls   | 08-Dec-2005
 Alger Falls     | 08-Dec-2005
...

To convert in the other direction (a character representation of a timestamp to a date), specify:

SELECT TO_DATE('8-Dec-2005', 'dd-mon-yyyy');

PostgreSQL closely follows Oracle in its support for format elements. Table 1-3 lists those available in PostgreSQL. Case follows form for alphabetic elements: use MON to yield JAN, FEB; Mon to yield Jan, Feb; and mon to yield jan, feb.

Warning

You cannot apply TO_CHAR to values of type TIME.

You can also use TO_TIMESTAMP to convert a Unix epoch value to a PostgreSQL timestamp:

SELECT TO_TIMESTAMP(0);

Unix time begins at midnight, at the beginning of 1-Jan-1970, Coordinated Universal Time (UTC).

Table 1-3. PostgreSQL datetime format elements

Element	Description
AM or PM A.M. or P.M.	Meridian indicator.
BC or AD B.C. or A.D.	B.C. or A.D. indicator.
CC	Century. Output-only.
D	Day in the week.
DAY, Day, or day	Name of day.
DD	Day in the month.
DDD	Day in the year.
DY, Dy, or dy	Abbreviated name of day.
FM	Toggles blank suppression. Output-only.
FX	Requires exact pattern matching on input.