extract
function
Applies to: Databricks SQL Databricks Runtime
Returns field
of source
.
Arguments
field
: A keyword that selects which part ofsource
should be extracted.source
: ADATE
,TIMESTAMP
, orINTERVAL
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
:
YEAR
,Y
,YEARS
,YR
,YRS
: The year fieldYEAROFWEEK
: 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 2004QUARTER
,QTR
: The quarter (1 - 4) of the year that the datetime falls inMONTH
,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 2013DAY
,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 (case-insensitive):
YEAR
,Y
,YEARS
,YR
,YRS
: The total months / 12MONTH
,MON
,MONS
,MONTHS
: The total months % 12DAY
,D
,DAYS
: The days part of intervalHOUR
,H
,HOURS
,HR
,HRS
: How many hours the microseconds containsMINUTE
,M
,MIN
,MINS
,MINUTES
: How many minutes left after taking hours from microsecondsSECOND
,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