Date & Time

DATE_TRUNC

DuckDB

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

Tipe hasil: TIMESTAMPDiperbarui: 6 Jan 2026

Syntax

SQL

DATE_TRUNC(part, date) | DATE_TRUNC(part, timestamp)

Parameter

partVARCHARwajib

Level presisi: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'

date/timestampDATE/TIMESTAMPwajib

Nilai tanggal atau timestamp yang akan di-truncate

Contoh Penggunaan

Truncate ke Bulan

SQL

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

Memotong ke awal bulan.

Hasil

2024-03-01 00:00:00

Aggregasi per Bulan

SQL

1 SELECT
2   DATE_TRUNC('month', order_date) as month,
3   COUNT(*) as total_orders,
4   SUM(amount) as total_revenue
5 FROM orders
6 GROUP BY DATE_TRUNC('month', order_date)
7 ORDER BY month;

Menghitung total order dan revenue per bulan.

Hasil

month	total_orders	total_revenue
2024-01-01	1250	125000000
2024-02-01	1380	138000000
2024-03-01	980	98000000

Analisis per Minggu

SQL

1 SELECT
2   DATE_TRUNC('week', created_at) as week_start,
3   COUNT(DISTINCT user_id) as weekly_active_users
4 FROM user_sessions
5 GROUP BY 1
6 ORDER BY 1 DESC
7 LIMIT 10;

Menghitung Weekly Active Users (WAU).

Truncate ke Jam

SQL

1 SELECT
2   DATE_TRUNC('hour', event_time) as hour,
3   COUNT(*) as events_per_hour
4 FROM events
5 WHERE event_date = CURRENT_DATE
6 GROUP BY 1
7 ORDER BY 1;

Menghitung distribusi event per jam.

Kembali ke DuckDB Functions

1	SELECT
2	DATE_TRUNC('month', order_date) as month,
3	COUNT(*) as total_orders,
4	SUM(amount) as total_revenue
5	FROM orders
6	GROUP BY DATE_TRUNC('month', order_date)
7	ORDER BY month;

1	SELECT
2	DATE_TRUNC('week', created_at) as week_start,
3	COUNT(DISTINCT user_id) as weekly_active_users
4	FROM user_sessions
5	GROUP BY 1
6	ORDER BY 1 DESC
7	LIMIT 10;

1	SELECT
2	DATE_TRUNC('hour', event_time) as hour,
3	COUNT(*) as events_per_hour
4	FROM events
5	WHERE event_date = CURRENT_DATE
6	GROUP BY 1
7	ORDER BY 1;