Date & Time

DATE_TRUNC

PostgreSQL

Memotong (truncate) timestamp ke presisi tertentu. Sangat berguna untuk grouping data berdasarkan periode waktu.

Tipe hasil: timestamp / timestamptz

Syntax

SQL

DATE_TRUNC(field, source [, time_zone])

Parameter

fieldtextwajib

Presisi truncate: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

sourcetimestamp/intervalwajib

Timestamp atau interval yang akan di-truncate

time_zonetextopsional

Opsional. Time zone untuk kalkulasi

Contoh Penggunaan

Truncate ke Bulan

SQL

1 SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-15 14:30:00');

Memotong timestamp ke awal bulan.

Hasil

2024-03-01 00:00:00

Grouping Sales per Bulan

SQL

1 SELECT
2   DATE_TRUNC('month', order_date) as month,
3   SUM(amount) as total_sales
4 FROM orders
5 GROUP BY DATE_TRUNC('month', order_date)
6 ORDER BY month;

Menghitung total penjualan per bulan.

Hasil

month	total_sales
2024-01-01 00:00:00	1250000
2024-02-01 00:00:00	1480000
2024-03-01 00:00:00	1320000

Truncate ke Jam untuk Time-Series

SQL

1 SELECT
2   DATE_TRUNC('hour', event_time) as hour,
3   COUNT(*) as event_count
4 FROM events
5 WHERE event_time >= NOW() - INTERVAL '24 hours'
6 GROUP BY 1
7 ORDER BY 1;

Menghitung jumlah event per jam dalam 24 jam terakhir.

Fungsi Equivalen di Database Lain

DATE_TRUNC

Snowflake

DATE_TRUNC

BigQuery

DATE_TRUNC

DuckDB

Kembali ke PostgreSQL Functions

1	SELECT
2	DATE_TRUNC('month', order_date) as month,
3	SUM(amount) as total_sales
4	FROM orders
5	GROUP BY DATE_TRUNC('month', order_date)
6	ORDER BY month;

1	SELECT
2	DATE_TRUNC('hour', event_time) as hour,
3	COUNT(*) as event_count
4	FROM events
5	WHERE event_time >= NOW() - INTERVAL '24 hours'
6	GROUP BY 1
7	ORDER BY 1;