Extraction Functions

This section contains extraction functions that extract unit from an input expression. The table below contains the supported units for the given extraction functions.

UnitDescription

YEAR

extracts year

QUARTER

extracts quarter

MONTH

extracts month

WEEK

extracts week of the year

DAY

extracts day

DOY

extract the day of the year

DOW

extract ISO day of the week (ranges from 1 (Monday) to 7 (Sunday)

HOUR

extracts hour

MINUTE

extracts minute

SECOND

extracts second

EPOCH

extracts epoch time

EXTRACT( unit FROM <datetime expr> )

Returns the value of specified unit from the given date-time expression.

> select extract(MINUTE FROM cast('2022-01-02 12:27:11' as timestamp))
27

DATEPART( unit, <expr> )

Returns the specified unit of the given date/timestamp expression.

> select current_date, datepart('day', current_date) as day_val;
----------------------------------------
| current_date        |day_val         |
----------------------------------------
| 2023-01-06 00:00:00 |6               |
----------------------------------------

WEEK( <date_or_timestamp_expr> )

Returns the week number of the year.

> select week(FROM_UNIXTIME_WITHUNIT(1673263327000, 'milliseconds'))
2

YEAR( <date_or_timestamp_expr> )

Returns the year for a specified date/timestamp.

> select week(FROM_UNIXTIME_WITHUNIT(1673263327000, 'milliseconds'))
2

LAST_DAY( <date_or_timestamp_expr> )

Return the last day of the month for a date or timestamp.

> select last_day(cast('2022-01-11' as date));
2022-01-31

DAYNAME( <date_or_timestamp_expr> )

Returns the three-letter day-of-week name from the given date or timestamp.

> select dayname(cast('2024-03-09' as date))
Sat
> select dayname(cast('2024-03-11' as timestamp))
Mon

Last updated