The EXTRACT SQL Function in MySQL and MariaDB - Extract unit from date or time |
|
| | EXTRACT | Syntax: | EXTRACT(Unit FROM Date) | | Return value: | INTEGER | | Function type: | Date and Time function | |
| | The EXTRACT() function extracts the "Unit" from the "Date".
The "Date" argument must be of type DATE, DATETIME or TIME.
If the value for "Date" is invalid or NULL, the EXTRACT() function returns NULL.
Possible values for "Unit":
| Unit | Description | Syntax |
| MICROSECOND | Microseconds | Number |
| SECOND | Seconds | Number |
| MINUTE | Minutes | Number |
| HOUR | Hours | Number |
| DAY | Days | Number |
| WEEK | Weeks | Number |
| MONTH | Months | Number |
| QUARTER | Quarters (3 months) | Number |
| YEAR | Years | Number |
| SECOND_MICROSECOND | Seconds and microseconds | '20:90' |
| MINUTE_MICROSECOND | Minutes, seconds and microseconds | '15:20:90' |
| MINUTE_SECOND | Minutes and seconds | '15:20' |
| HOUR_MICROSECOND | Hours, minutes, seconds and microseconds | '3:15:20:90' |
| HOUR_SECOND | Hours, minutes and seconds | '3:15:20' |
| HOUR_MINUTE | Hours and minutes | '3:15' |
| DAY_MICROSECOND | Days, hours, minutes, seconds and microseconds | '2:3:15:20:90' |
| DAY_SECOND | Days, hours, minutes and seconds | '2:3:15:20' |
| DAY_MINUTE | Days, hours and minutes | '3:15:5' |
| DAY_HOUR | Days and hours | '15:5' |
| YEAR_MONTH | Years and months | '3:4' | | SQL Examples for the EXTRACT function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
| SELECT now(), extract(year from now());
SELECT now(), extract(quarter from now());
SELECT now(), extract(month from now());
SELECT now(), extract(week from now());
SELECT now(), extract(day from now());
SELECT now(), extract(hour from now());
SELECT now(), extract(minute from now());
SELECT now(), extract(second from now());
SELECT now(6), extract(microsecond from now(6));
select utc_timestamp(), extract(hour from utc_timestamp());
SELECT now(6), extract(HOUR_MICROSECOND from now(6));
SELECT now(6), extract(DAY_HOUR from now(6));
select extract(hour from '0000-00-00 00:00:00');
select extract(hour from 'xxx');
select extract(hour from null);
|
|
| now() |  |
| extract(year from now()) |
|---|
| datetime(19) | int(4) |
|---|
| 04.04.2023 18:27:16 | 2023 |
|
|
|
|
| now() | |
| extract(quarter from now()) |
|---|
| datetime(19) | int(2) |
|---|
| 04.04.2023 18:27:16 | 2 |
|
|
|
|
| now() | |
| extract(month from now()) |
|---|
| datetime(19) | int(2) |
|---|
| 04.04.2023 18:27:16 | 4 |
|
|
|
|
| now() | |
| extract(week from now()) |
|---|
| datetime(19) | int(2) |
|---|
| 04.04.2023 18:27:16 | 14 |
|
|
|
|
| now() | |
| extract(day from now()) |
|---|
| datetime(19) | int(3) |
|---|
| 04.04.2023 18:27:16 | 4 |
|
|
|
|
| now() | |
| extract(hour from now()) |
|---|
| datetime(19) | int(3) |
|---|
| 04.04.2023 18:27:16 | 18 |
|
|
|
|
| now() | |
| extract(minute from now()) |
|---|
| datetime(19) | int(3) |
|---|
| 04.04.2023 18:27:16 | 27 |
|
|
|
|
| now() | |
| extract(second from now()) |
|---|
| datetime(19) | int(3) |
|---|
| 04.04.2023 18:27:16 | 16 |
|
|
|
|
| now(6) | |
| extract(microsecond from now(6)) |
|---|
| datetime(26) | int(7) |
|---|
| 2023-04-04 18:27:16.276541 | 276541 |
|
|
|
|
| utc_timestamp() | |
| extract(hour from utc_timestamp()) |
|---|
| datetime(19) | int(3) |
|---|
| 04.04.2023 16:27:16 | 16 |
|
|
|
|
| now(6) | |
| extract(HOUR_MICROSECOND from now(6)) |
|---|
| datetime(26) | bigint(13) |
|---|
| 2023-04-04 18:27:16.278775 | 182716278775 |
|
|
|
|
| now(6) | |
| extract(DAY_HOUR from now(6)) |
|---|
| datetime(26) | int(5) |
|---|
| 2023-04-04 18:27:16.280453 | 418 |
|
|
|
|
| extract(hour from '0000-00-00 00:00:00') |
|---|
| int(3) |
|---|
| 0 |
|
|
| extract(hour from 'xxx') |
|---|
| int(3) |
|---|
| NULL |
|
|
| extract(hour from null) |
|---|
| int(3) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the EXTRACT() function in MySQL and MariaDB databases | | MySQL and MariaDB contain the EXTRACT() function, which is used to extract a specific part (year, month, day, hour, etc.) from a "date" or "date and time" expression. Column names or other expressions can also be used as parameters in the function to retrieve individual date or time components of a value in queries, where the function returns the extracted component as an integer value or as a string, based on the chosen unit. In queries, you can work with individual components of date and time information and perform various operations on the basis of these components. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the EXTRACT SQL function: and and |
|
|
|
|
