Dasar -dasar Query PostgreSQL

Menampilkan bagian tanggal tertentu jam, hari, menit :

Formula dasar :

EXTRACT(field FROM source)

Field mengandung :

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

Sample : 

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500 

Pembulatan waktu :

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00 

Lihat Waktu saat ini :

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)

Sample : 

SELECT CURRENT_DATE;
Result: 2001-12-23

Format Angka menjadi TEXT :

to_char(1, '999')

Result: 001