Extraction Functions

Retrieve specific components from dates, times, and strings.

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

Unit	Description
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 date or timestamp.

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

MONTH( <date_or_timestamp_expr> )

Returns the month for a date or timestamp.

> select month(date('2002-11-14'));
11

DAYS( <date_or_timestamp_expr> )

Returns the day of the month for a date or timestamp.

> select days(to_timestamp('2023-04-18 10:30:00'));
18

> select days(to_date('2023-04-12'));
12

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 for a date or timestamp.

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

HOUR( <date_or_timestamp_expr> )

Returns the hour of the day from timestamp. The value ranges from 0 to 23.

> select hour(to_timestamp('2023-04-18 10:30:00'));
10

MINUTE( <date_or_timestamp_expr> )

Returns the minute of the hour from timestamp. The value ranges from 0 to 59.

> select minute(to_timestamp('2023-04-18 10:30:00'));
30

SECOND( <date_or_timestamp_expr> )

Returns the second of the minute from timestamp. The value ranges from 0 to 59.

> select second(to_timestamp('2023-04-18 10:30:45'));
45

DAYOFWEEKISO( <date_or_timestamp_expr> )

Day (number) of the week uses ISO semantics 1 to 7

> select DAYOFWEEKISO(tstamp) AS "DAYOFWEEKISO" from ( SELECT '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp)
6

WEEKOFYEAR( <date_or_timestamp_expr> )

Week (number) of the year 1 to 53.

> SELECT WEEKOFYEAR(tstamp) AS "WEEK OF YEAR" FROM (SELECT  '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp)
53

WEEKISO( <date_or_timestamp_expr> )

WEEKISO is the same as WEEKOFYEAR, except uses ISO semantics 1 to 53.

> SELECT WEEKISO(tstamp) AS "WEEK ISO" FROM (SELECT  '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp)
53