Date & Time

EXTRACT

BigQuery

Mengekstrak komponen tertentu dari DATE, TIME, DATETIME, atau TIMESTAMP. Mengembalikan nilai integer.

Tipe hasil: INT64Diperbarui: 7 Jan 2026

Syntax

SQL

EXTRACT(part FROM date_expression) | EXTRACT(part FROM timestamp_expression AT TIME ZONE time_zone)

Parameter

partDATE_PARTwajib

Bagian yang diekstrak: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYOFWEEK, etc.

date_expressionDATE | TIME | DATETIME | TIMESTAMPwajib

Nilai tanggal/waktu sumber

time_zoneSTRINGopsional

Timezone untuk TIMESTAMP (opsional)

Contoh Penggunaan

Extract Date Parts

SQL

1 SELECT
2   date,
3   EXTRACT(YEAR FROM date) as year,
4   EXTRACT(MONTH FROM date) as month,
5   EXTRACT(DAY FROM date) as day,
6   EXTRACT(DAYOFWEEK FROM date) as dow
7 FROM UNNEST([DATE '2024-01-15']) as date;

Ekstrak komponen dari DATE.

Hasil

date	year	month	day	dow
2024-01-15	2024	1	15	2

Extract from Timestamp with Timezone

SQL

1 SELECT
2   EXTRACT(HOUR FROM ts AT TIME ZONE 'Asia/Jakarta') as hour_jakarta,
3   EXTRACT(HOUR FROM ts AT TIME ZONE 'UTC') as hour_utc
4 FROM UNNEST([TIMESTAMP '2024-01-15 10:00:00 UTC']) as ts;

Ekstrak jam dengan timezone berbeda.

Hasil

hour_jakarta	hour_utc
17	10

Group by Day of Week

SQL

1 SELECT
2   EXTRACT(DAYOFWEEK FROM order_date) as dow,
3   CASE EXTRACT(DAYOFWEEK FROM order_date)
4     WHEN 1 THEN 'Minggu'
5     WHEN 2 THEN 'Senin'
6     WHEN 3 THEN 'Selasa'
7     WHEN 4 THEN 'Rabu'
8     WHEN 5 THEN 'Kamis'
9     WHEN 6 THEN 'Jumat'
10     WHEN 7 THEN 'Sabtu'
11   END as day_name,
12   COUNT(*) as orders
13 FROM `project.dataset.orders`
14 GROUP BY dow, day_name
15 ORDER BY dow;

Analisis order per hari dalam seminggu.

Kembali ke BigQuery Functions

1	SELECT
2	date,
3	EXTRACT(YEAR FROM date) as year,
4	EXTRACT(MONTH FROM date) as month,
5	EXTRACT(DAY FROM date) as day,
6	EXTRACT(DAYOFWEEK FROM date) as dow
7	FROM UNNEST([DATE '2024-01-15']) as date;

1	SELECT
2	EXTRACT(HOUR FROM ts AT TIME ZONE 'Asia/Jakarta') as hour_jakarta,
3	EXTRACT(HOUR FROM ts AT TIME ZONE 'UTC') as hour_utc
4	FROM UNNEST([TIMESTAMP '2024-01-15 10:00:00 UTC']) as ts;

1	SELECT
2	EXTRACT(DAYOFWEEK FROM order_date) as dow,
3	CASE EXTRACT(DAYOFWEEK FROM order_date)
4	WHEN 1 THEN 'Minggu'
5	WHEN 2 THEN 'Senin'
6	WHEN 3 THEN 'Selasa'
7	WHEN 4 THEN 'Rabu'
8	WHEN 5 THEN 'Kamis'
9	WHEN 6 THEN 'Jumat'
10	WHEN 7 THEN 'Sabtu'
11	END as day_name,
12	COUNT(*) as orders
13	FROM `project.dataset.orders`
14	GROUP BY dow, day_name
15	ORDER BY dow;