Date & Time

EXTRACT

BigQuery

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

Tipe hasil: INT64Diperbarui: 16 Mei 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.

Pertanyaan Umum tentang EXTRACT

Apa itu fungsi EXTRACT di BigQuery?

Mengekstrak komponen tertentu dari DATE, TIME, DATETIME, atau TIMESTAMP. Mengembalikan nilai integer. Di BigQuery, fungsi EXTRACT termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.

Bagaimana cara menggunakan EXTRACT di BigQuery?

Gunakan sintaks berikut: EXTRACT(part FROM date_expression) | EXTRACT(part FROM timestamp_expression AT TIME ZONE time_zone). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.

Apa nilai yang dikembalikan oleh fungsi EXTRACT?

Fungsi EXTRACT mengembalikan nilai bertipe INT64. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda — selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.

Apa saja parameter fungsi EXTRACT?

Fungsi ini memiliki 2 parameter wajib dan 1 parameter opsional. Parameter yang digunakan: part (DATE_PART, wajib): Bagian yang diekstrak: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYOFWEEK, etc.; date_expression (DATE | TIME | DATETIME | TIMESTAMP, wajib): Nilai tanggal/waktu sumber; time_zone (STRING, opsional): Timezone untuk TIMESTAMP (opsional).

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;