extract function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns field of source.

Syntax 

extract(field FROM source)

Arguments

  • field: A keyword that selects which part of source should be extracted.

  • source: A DATE, TIMESTAMP, or INTERVAL expression.

Returns

If field is SECOND, a DECIMAL(8, 6). In all other cases, an INTEGER.

Supported values of field when source is DATE or TIMESTAMP are:

  • “YEAR”, (“Y”, “YEARS”, “YR”, “YRS”) - the year field

  • “YEAROFWEEK” - the ISO 8601 week-numbering year that the datetime falls in. For example, 2005-01-02 is part of the 53rd week of year 2004, so the result is 2004

  • “QUARTER”, (“QTR”) - the quarter (1 - 4) of the year that the datetime falls in

  • “MONTH”, (“MON”, “MONS”, “MONTHS”) - the month field (1 - 12)

  • “WEEK”, (“W”, “WEEKS”) - the number of the ISO 8601 week-of-week-based-year. A week is considered to start on a Monday and week 1 is the first week with >3 days. In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-02 is part of the 53rd week of year 2004, while 2012-12-31 is part of the first week of 2013

  • “DAY”, (“D”, “DAYS”) - the day of the month field (1 - 31)

  • “DAYOFWEEK”,(“DOW”) - the day of the week for datetime as Sunday(1) to Saturday(7)

  • “DAYOFWEEK_ISO”,(“DOW_ISO”) - ISO 8601 based day of the week for datetime as Monday(1) to Sunday(7)

  • “DOY” - the day of the year (1 - 365/366)

  • “HOUR”, (“H”, “HOURS”, “HR”, “HRS”) - The hour field (0 - 23)

  • “MINUTE”, (“M”, “MIN”, “MINS”, “MINUTES”) - the minutes field (0 - 59)

  • “SECOND”, (“S”, “SEC”, “SECONDS”, “SECS”) - the seconds field, including fractional parts

Supported values of field when source is INTERVAL are:

  • “YEAR”, (“Y”, “YEARS”, “YR”, “YRS”) - the total months / 12

  • “MONTH”, (“MON”, “MONS”, “MONTHS”) - the total months % 12

  • “DAY”, (“D”, “DAYS”) - the days part of interval

  • “HOUR”, (“H”, “HOURS”, “HR”, “HRS”) - how many hours the microseconds contains

  • “MINUTE”, (“M”, “MIN”, “MINS”, “MINUTES”) - how many minutes left after taking hours from microseconds

  • “SECOND”, (“S”, “SEC”, “SECONDS”, “SECS”) - how many seconds with fractions left after taking hours and minutes from microseconds

Examples 

> SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456');
 2019

> SELECT extract(week FROM TIMESTAMP'2019-08-12 01:00:00.123456');
 33

> SELECT extract(DAY FROM DATE'2019-08-12');
 12

> SELECT extract(SECONDS FROM TIMESTAMP'2019-10-01 00:00:01.000001');
 1.000001

> SELECT extract(MONTHS FROM INTERVAL '2-11' YEAR TO MONTH);
 11

> SELECT extract(SECONDS FROM INTERVAL '5:00:30.001' HOUR TO SECOND);
 30.001000