DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference Development reference: SQL Function reference Built-in functions Date and Time Functions EXTRACT

EXTRACT

Updated at:
Copy as MD
Product
My Favorites

Extracts a specific date component from a date value. EXTRACT is an additional function of MaxCompute V2.0.

Syntax

int extract(<datepart> from <date>)

extract(<datepart> from <date>) → INT

Prerequisites

EXTRACT requires the MaxCompute V2.0 data type edition. Run the following flag in the same commit as your SQL statement:

set odps.sql.type.system.odps2=true;

Parameters

datepart

The date component to extract. Accepted values:

ValueDescription
YEARReturns the year
MONTHReturns the month
DAYReturns the day of the month
HOURReturns the hour
MINUTEReturns the minute

Any other value, or a null value, causes an error.

date

The date value to extract from. Accepted types:

TypeAccepted formats
DATEyyyy-mm-dd
DATETIMEyyyy-mm-dd hh:mi:ss
TIMESTAMPyyyy-mm-dd hh:mi:ss.ff3
STRINGMust include at least the yyyy-mm-dd part, with no extra strings appended

If date is null or is not one of the accepted types, EXTRACT returns null.

Return value

Returns an INT representing the extracted date component.

Examples

Static values

-- Run this flag together with the SQL statement.
set odps.sql.type.system.odps2=true;
select
    extract(year   from '2019-05-01 11:21:00') as year,
    extract(month  from '2019-05-01 11:21:00') as month,
    extract(day    from '2019-05-01 11:21:00') as day,
    extract(hour   from '2019-05-01 11:21:00') as hour,
    extract(minute from '2019-05-01 11:21:00') as minute;
-- The following result is returned:
-- +------+-------+------+------+--------+
-- | year | month | day  | hour | minute |
-- +------+-------+------+------+--------+
-- | 2019 | 5     | 1    | 11   | 21     |
-- +------+-------+------+------+--------+

-- Returns null when the input is null.
select extract(year from null);

Table data

The following example extracts the year from timestamp1 and the month from timestamp2 in the mf_date_fun_t table. For the sample table definition and data, see Sample data.

-- Run this flag together with the SQL statement.
set odps.sql.type.system.odps2=true;
select
    timestamp1,
    extract(year  from timestamp1) as year,
    timestamp2,
    extract(month from timestamp2) as month
from mf_date_fun_t;
-- The following result is returned:
-- +-------------------------------+------+-------------------------------+-------+
-- | timestamp1                    | year | timestamp2                    | month |
-- +-------------------------------+------+-------------------------------+-------+
-- | 2021-01-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-02-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-03-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-04-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-05-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-06-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-07-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-08-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-09-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- | 2021-10-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
-- +-------------------------------+------+-------------------------------+-------+

Sample data

The examples in this topic use the mf_date_fun_t table. Run the following statements to create the table and insert sample data:

create table if not exists mf_date_fun_t(
    id         int,
    date1      date,
    datetime1  datetime,
    timestamp1 timestamp,
    date2      date,
    datetime2  datetime,
    timestamp2 timestamp,
    date3      string,
    date4      bigint);

insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);

To verify the data was inserted correctly:

select * from mf_date_fun_t;
-- The following result is returned:
-- +------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+
-- | id   | date1      | datetime1           | timestamp1                    | date2      | datetime2           | timestamp2                    | date3      | date4      |
-- +------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+
-- | 1    | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780  |
-- | 2    | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781  |
-- | 3    | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782  |
-- | 4    | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783  |
-- | 5    | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784  |
-- | 6    | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785  |
-- | 7    | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786  |
-- | 8    | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787  |
-- | 9    | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788  |
-- | 10   | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-29 | 123456789  |
-- +------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+

Related functions

EXTRACT is a date function. For other date and time functions, see Date functions.

Previous：DAYOFYEARNext：FORMAT_TIME