Date & Time

EXTRACT

BigQuery

Extracts a specific component from a DATE, TIME, DATETIME, or TIMESTAMP value. Returns an integer.

Tipe hasil: INT64Diperbarui: 13 Jun 2026

Syntax

SQL

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

Parameter

partDATE_PARTwajib

The part to extract: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYOFWEEK, etc.

date_expressionDATE | TIME | DATETIME | TIMESTAMPwajib

The source date/time value

time_zoneSTRINGopsional

Timezone for TIMESTAMP extraction (optional)

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;

Extract individual components from a DATE value.

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;

Extract the hour component using different timezones.

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 'Sunday'
5     WHEN 2 THEN 'Monday'
6     WHEN 3 THEN 'Tuesday'
7     WHEN 4 THEN 'Wednesday'
8     WHEN 5 THEN 'Thursday'
9     WHEN 6 THEN 'Friday'
10     WHEN 7 THEN 'Saturday'
11   END as day_name,
12   COUNT(*) as orders
13 FROM `project.dataset.orders`
14 GROUP BY dow, day_name
15 ORDER BY dow;

Analyze order volume by day of the week.

Pertanyaan Umum tentang EXTRACT

Apa itu fungsi EXTRACT di BigQuery?

Extracts a specific component from a DATE, TIME, DATETIME, or TIMESTAMP value. Returns an 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): The part to extract: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYOFWEEK, etc.; date_expression (DATE | TIME | DATETIME | TIMESTAMP, wajib): The source date/time value; time_zone (STRING, opsional): Timezone for TIMESTAMP extraction (optional).

Ngulik Langsung

Sudah paham EXTRACT? Latih langsung di browser

Latihan interaktif, langsung di browser.

Latihan Langsung →

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 'Sunday'
5	WHEN 2 THEN 'Monday'
6	WHEN 3 THEN 'Tuesday'
7	WHEN 4 THEN 'Wednesday'
8	WHEN 5 THEN 'Thursday'
9	WHEN 6 THEN 'Friday'
10	WHEN 7 THEN 'Saturday'
11	END as day_name,
12	COUNT(*) as orders
13	FROM `project.dataset.orders`
14	GROUP BY dow, day_name
15	ORDER BY dow;