The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table below list them.  These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

The to_timestamp function can also take a single double precision argument to convert from Unix epoch to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

Function Return Type Description Example
to_char(timestamp, text) text convert time stamp to string to_char(current_timestamp,
'HH12:MI:SS')
to_char(interval,
text)
text convert interval to string to_char(interval
'15h 2m 12s', 'HH24:MI:SS')
to_char(int,
text)
text convert integer to string to_char(125, '999')
to_char(double
precision
, text)
text convert real/double precision to string to_char(125.8::real,
'999D9')
to_char(numeric,
text)
text convert numeric to string to_char(-125.8,
'999D99S')
to_date(text,
text)
date convert string to date to_date('05 Dec 2000',
'DD Mon YYYY')
to_number(text,
text)
numeric convert string to numeric to_number('12,454.8-',
'99G999D9S')
to_timestamp(text, text) timestamp with time zone convert string to time stamp to_timestamp('05 Dec 2000',
'DD Mon YYYY')
to_timestamp(double
precision
)
timestamp with time zone convert UNIX epoch to time stamp to_timestamp(200120400)

I try to use to_timestamp, and need template pattern for Date/Time formatting. Here the template:

Pattern Description
HH hour of day (01-12)
HH12 hour of day (01-12)
HH24 hour of day (00-23)
MI minute (00-59)
SS second (00-59)
MS millisecond (000-999)
US microsecond (000000-999999)
SSSS seconds past midnight (0-86399)
AM or A.M. or PM or
P.M.
meridian indicator (uppercase)
am or a.m. or pm or
p.m.
meridian indicator (lowercase)
Y,YYY year (4 and more digits) with comma
YYYY year (4 and more digits)
YYY last 3 digits of year
YY last 2 digits of year
Y last digit of year
IYYY ISO year (4 and more digits)
IYY last 3 digits of ISO year
IY last 2 digits of ISO year
I last digits of ISO year
BC or B.C. or AD or
A.D.
era indicator (uppercase)
bc or b.c. or ad or
a.d.
era indicator (lowercase)
MONTH full uppercase month name (blank-padded to 9
chars)
Month full mixed-case month name (blank-padded to 9
chars)
month full lowercase month name (blank-padded to 9
chars)
MON abbreviated uppercase month name (3 chars in English,
localized lengths vary)
Mon abbreviated mixed-case month name (3 chars in
English, localized lengths vary)
mon abbreviated lowercase month name (3 chars in English,
localized lengths vary)
MM month number (01-12)
DAY full uppercase day name (blank-padded to 9
chars)
Day full mixed-case day name (blank-padded to 9
chars)
day full lowercase day name (blank-padded to 9
chars)
DY abbreviated uppercase day name (3 chars in English,
localized lengths vary)
Dy abbreviated mixed-case day name (3 chars in English,
localized lengths vary)
dy abbreviated lowercase day name (3 chars in English,
localized lengths vary)
DDD day of year (001-366)
DD day of month (01-31)
D day of week (1-7; Sunday is 1)
W week of month (1-5) (The first week starts on the
first day of the month.)
WW week number of year (1-53) (The first week starts on
the first day of the year.)
IW ISO week number of year (The first Thursday of the
new year is in week 1.)
CC century (2 digits) (The twenty-first century starts
on 2001-01-01.)
J Julian Day (days since January 1, 4712 BC)
Q quarter
RM month in Roman numerals (I-XII; I=January)
(uppercase)
rm month in Roman numerals (i-xii; i=January)
(lowercase)
TZ time-zone name (uppercase)
tz time-zone name (lowercase)