Satu hari ada kebutuhan untuk query ke database postgreSQL, dengan where condition waktu tertentu pada kolom yg bertipe timestamp. Simple sih, tapi fungsi konverternya? Kagak hapal gw:mrgreen:

Setelah bertanya sama mbah gugel, akhirnya gw hapal (cuma hapal 1 wkwk) tapi itu udah cukup sakti buat query-query ke kolom bertipe timestamp di postgtreSQL. Fungsi tsb bernama to_timestamp().

Definisinya: to_timestamp(text, text), dimana text pertama berisi tanggal dan/atau jam dan text kedua berisi format yg digunakan. Berikut ini contohnya:

to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp('05 Dec 2000 7:00:00 AM', 'DD Mon YYYY HH:MI:SS AM')

Untuk template pattern yg bisa digunakan untuk format tanggal/jam di parameter kedua:

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)
Mon abbreviated mixed-case month name (3 chars)
mon abbreviated lowercase month name (3 chars)
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)
Dy abbreviated mixed-case day name (3 chars)
dy abbreviated lowercase day name (3 chars)
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)
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)

Sedangkan berikut ini fungsi formatter yg lain yg bisa digunakan:

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_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)
to_number (text,
text)
numeric convert string to numeric to_number(‘12,454.8-‘,
’99G999D9S’)

Yup, kurang lebih segitu sih. Kalau mau lihat lebih lengkap, silakan ke sumbernya.