MySQL Tutorial/Date Time Functions/EXTRACT
Содержание
- 1 EXTRACT(unit FROM date): extracts parts from the date rather than performing date arithmetic
- 2 SELECT EXTRACT(DAY FROM "2003-30:00.000123");
- 3 SELECT EXTRACT(DAY_HOUR FROM "2003-30:00.000123");
- 4 SELECT EXTRACT(DAY_MICROSECOND FROM "2003-30:00.000123");
- 5 SELECT EXTRACT(DAY_MINUTE FROM "1999-02:03");
- 6 SELECT EXTRACT(DAY_SECOND FROM "2003-30:00.000123");
- 7 SELECT EXTRACT(HOUR FROM "2003-30:00.000123");
- 8 SELECT EXTRACT(HOUR_MICROSECOND FROM "2003-30:00.000123");
- 9 SELECT EXTRACT(HOUR_MINUTE FROM "2003-30:00.000123");
- 10 SELECT EXTRACT(HOUR_SECOND FROM "2003-30:00.000123");
- 11 SELECT EXTRACT(MICROSECOND FROM "2003-30:00.000123");
- 12 SELECT EXTRACT(MINUTE FROM "2003-30:00.000123");
- 13 SELECT EXTRACT(MINUTE_MICROSECOND FROM "2003-30:00.000123");
- 14 SELECT EXTRACT(MINUTE_SECOND FROM "2003-30:00.000123");
- 15 SELECT EXTRACT(MONTH FROM "2003-30:00.000123");
- 16 SELECT EXTRACT(QUARTER FROM "2003-30:00.000123");
- 17 SELECT EXTRACT(SECOND FROM "2003-30:00.000123");
- 18 SELECT EXTRACT(SECOND_MICROSECOND FROM "2003-30:00.000123");
- 19 SELECT EXTRACT(WEEK FROM "2003-30:00.000123");
- 20 SELECT EXTRACT(YEAR FROM "1999-07-02");
- 21 SELECT EXTRACT(YEAR_MONTH FROM "1999-02:03");
EXTRACT(unit FROM date): extracts parts from the date rather than performing date arithmetic
The following table shows the expected form of the expr argument for each unit value.
unit Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND "SECONDS.MICROSECONDS" MINUTE_MICROSECOND "MINUTES.MICROSECONDS" MINUTE_SECOND "MINUTES:SECONDS" HOUR_MICROSECOND "HOURS.MICROSECONDS" HOUR_SECOND "HOURS:MINUTES:SECONDS" HOUR_MINUTE "HOURS:MINUTES" DAY_MICROSECOND "DAYS.MICROSECONDS" DAY_SECOND "DAYS HOURS:MINUTES:SECONDS" DAY_MINUTE "DAYS HOURS:MINUTES" DAY_HOUR "DAYS HOURS" YEAR_MONTH "YEARS-MONTHS"
SELECT EXTRACT(DAY FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(DAY FROM "2003-01-02 10:30:00.000123");
+------------------------------------------------+
| EXTRACT(DAY FROM "2003-01-02 10:30:00.000123") |
+------------------------------------------------+
| 2 |
+------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(DAY_HOUR FROM "2003-30:00.000123");
mysql>
mysql> SELECT EXTRACT(DAY_HOUR FROM "2003-01-02 10:30:00.000123");
+-----------------------------------------------------+
| EXTRACT(DAY_HOUR FROM "2003-01-02 10:30:00.000123") |
+-----------------------------------------------------+
| 210 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
SELECT EXTRACT(DAY_MICROSECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(DAY_MICROSECOND FROM "2003-01-02 10:30:00.000123");
+------------------------------------------------------------+
| EXTRACT(DAY_MICROSECOND FROM "2003-01-02 10:30:00.000123") |
+------------------------------------------------------------+
| 2103000000123 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
SELECT EXTRACT(DAY_MINUTE FROM "1999-02:03");
mysql>
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03") |
+------------------------------------------------+
| 20102 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
SELECT EXTRACT(DAY_SECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(DAY_SECOND FROM "2003-01-02 10:30:00.000123");
+-------------------------------------------------------+
| EXTRACT(DAY_SECOND FROM "2003-01-02 10:30:00.000123") |
+-------------------------------------------------------+
| 2103000 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(HOUR FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(HOUR FROM "2003-01-02 10:30:00.000123");
+-------------------------------------------------+
| EXTRACT(HOUR FROM "2003-01-02 10:30:00.000123") |
+-------------------------------------------------+
| 10 |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(HOUR_MICROSECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM "2003-01-02 10:30:00.000123");
+-------------------------------------------------------------+
| EXTRACT(HOUR_MICROSECOND FROM "2003-01-02 10:30:00.000123") |
+-------------------------------------------------------------+
| 103000000123 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(HOUR_MINUTE FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(HOUR_MINUTE FROM "2003-01-02 10:30:00.000123");
+--------------------------------------------------------+
| EXTRACT(HOUR_MINUTE FROM "2003-01-02 10:30:00.000123") |
+--------------------------------------------------------+
| 1030 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(HOUR_SECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(HOUR_SECOND FROM "2003-01-02 10:30:00.000123");
+--------------------------------------------------------+
| EXTRACT(HOUR_SECOND FROM "2003-01-02 10:30:00.000123") |
+--------------------------------------------------------+
| 103000 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(MICROSECOND FROM "2003-30:00.000123");
mysql>
mysql> SELECT EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.000123");
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.000123") |
+--------------------------------------------------------+
| 123 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
SELECT EXTRACT(MINUTE FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(MINUTE FROM "2003-01-02 10:30:00.000123");
+---------------------------------------------------+
| EXTRACT(MINUTE FROM "2003-01-02 10:30:00.000123") |
+---------------------------------------------------+
| 30 |
+---------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(MINUTE_MICROSECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM "2003-01-02 10:30:00.000123");
+---------------------------------------------------------------+
| EXTRACT(MINUTE_MICROSECOND FROM "2003-01-02 10:30:00.000123") |
+---------------------------------------------------------------+
| 3000000123 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(MINUTE_SECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(MINUTE_SECOND FROM "2003-01-02 10:30:00.000123");
+----------------------------------------------------------+
| EXTRACT(MINUTE_SECOND FROM "2003-01-02 10:30:00.000123") |
+----------------------------------------------------------+
| 3000 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
SELECT EXTRACT(MONTH FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(MONTH FROM "2003-01-02 10:30:00.000123");
+--------------------------------------------------+
| EXTRACT(MONTH FROM "2003-01-02 10:30:00.000123") |
+--------------------------------------------------+
| 1 |
+--------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(QUARTER FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(QUARTER FROM "2003-01-02 10:30:00.000123");
+----------------------------------------------------+
| EXTRACT(QUARTER FROM "2003-01-02 10:30:00.000123") |
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(SECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql>
mysql> SELECT EXTRACT(SECOND FROM "2003-01-02 10:30:00.000123");
+---------------------------------------------------+
| EXTRACT(SECOND FROM "2003-01-02 10:30:00.000123") |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(SECOND_MICROSECOND FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM "2003-01-02 10:30:00.000123");
+---------------------------------------------------------------+
| EXTRACT(SECOND_MICROSECOND FROM "2003-01-02 10:30:00.000123") |
+---------------------------------------------------------------+
| 123 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(WEEK FROM "2003-30:00.000123");
mysql>
mysql>
mysql> SELECT EXTRACT(WEEK FROM "2003-01-02 10:30:00.000123");
+-------------------------------------------------+
| EXTRACT(WEEK FROM "2003-01-02 10:30:00.000123") |
+-------------------------------------------------+
| 0 |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT EXTRACT(YEAR FROM "1999-07-02");
mysql>
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
+---------------------------------+
| EXTRACT(YEAR FROM "1999-07-02") |
+---------------------------------+
| 1999 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
SELECT EXTRACT(YEAR_MONTH FROM "1999-02:03");
mysql>
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03") |
+------------------------------------------------+
| 199907 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>