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 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

Last updated

#930: Cross account hive GCP

Change request updated