Date & Time

EXTRACT

Snowflake

Mengekstrak komponen tertentu dari timestamp atau date, seperti tahun, bulan, hari, jam, dll.

Tipe hasil: INTEGER/FLOATDiperbarui: 6 Jan 2026

Syntax

SQL

EXTRACT(date_or_time_part FROM date_or_time_expr) | DATE_PART(date_or_time_part, date_or_time_expr)

Parameter

date_or_time_partVARCHARwajib

Komponen yang diekstrak: YEAR, MONTH, DAY, DAYOFWEEK, DAYOFYEAR, WEEK, QUARTER, HOUR, MINUTE, SECOND, EPOCH

date_or_time_exprDATE/TIMESTAMPwajib

Nilai tanggal atau timestamp sumber

Contoh Penggunaan

Mengekstrak Tahun dan Bulan

SQL

1 SELECT
2   order_date,
3   EXTRACT(YEAR FROM order_date) as year,
4   EXTRACT(MONTH FROM order_date) as month,
5   EXTRACT(DAY FROM order_date) as day
6 FROM orders;

Mengambil komponen tahun, bulan, dan hari dari order_date.

Hasil

ORDER_DATE	YEAR	MONTH	DAY
2024-03-15	2024	3	15
2024-02-28	2024	2	28

Filter Weekend

SQL

1 SELECT *
2 FROM sales
3 WHERE EXTRACT(DAYOFWEEK FROM sale_date) IN (0, 6);

Mengambil data penjualan di akhir pekan (Minggu = 0, Sabtu = 6).

Analisis per Quarter

SQL

1 SELECT
2   EXTRACT(YEAR FROM order_date) as year,
3   EXTRACT(QUARTER FROM order_date) as quarter,
4   SUM(amount) as total_revenue
5 FROM orders
6 GROUP BY 1, 2
7 ORDER BY 1, 2;

Menghitung total revenue per quarter.

Hasil

YEAR	QUARTER	TOTAL_REVENUE
2024	1	3500000
2024	2	4200000

Shorthand Functions

SQL

1 SELECT
2   order_date,
3   YEAR(order_date) as year,
4   MONTH(order_date) as month,
5   DAY(order_date) as day,
6   HOUR(order_timestamp) as hour
7 FROM orders;

Snowflake menyediakan shorthand functions untuk EXTRACT.

Fungsi Terkait

DATE_PARTYEARMONTHDAYHOURMINUTESECONDQUARTERWEEK

Kembali ke Snowflake Functions

1	SELECT
2	order_date,
3	EXTRACT(YEAR FROM order_date) as year,
4	EXTRACT(MONTH FROM order_date) as month,
5	EXTRACT(DAY FROM order_date) as day
6	FROM orders;

1	SELECT *
2	FROM sales
3	WHERE EXTRACT(DAYOFWEEK FROM sale_date) IN (0, 6);

1	SELECT
2	EXTRACT(YEAR FROM order_date) as year,
3	EXTRACT(QUARTER FROM order_date) as quarter,
4	SUM(amount) as total_revenue
5	FROM orders
6	GROUP BY 1, 2
7	ORDER BY 1, 2;

1	SELECT
2	order_date,
3	YEAR(order_date) as year,
4	MONTH(order_date) as month,
5	DAY(order_date) as day,
6	HOUR(order_timestamp) as hour
7	FROM orders;