MySQL: DATE

2024-06-01 21:30| 来源: 网络整理| 查看: 265

MySQL: DATE_SUB Function

This MySQL tutorial explains how to use the MySQL DATE_SUB function with syntax and examples.

Description

The MySQL DATE_SUB function returns a date after which a certain time/date interval has been subtracted.

Syntax

The syntax for the DATE_SUB function in MySQL is:

DATE_SUB( date, INTERVAL value unit ) Parameters or Arguments date The date to which the interval should be subtracted. value The value of the time/date interval that you wish to subtract. You can specify positive and negative values for this parameter. unit

The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following:

unit Compatibility MICROSECOND 4.1.1+ SECOND 3.2.3+ MINUTE 3.2.3+ HOUR 3.2.3+ DAY 3.2.3+ WEEK 5+ MONTH 3.2.3+ QUARTER 5+ YEAR 3.2.3+ SECOND_MICROSECOND 4.1.1+ MINUTE_MICROSECOND 4.1.1+ MINUTE_SECOND 4.1.1+ HOUR_MICROSECOND 4.1.1+ HOUR_SECOND 4.1.1+ HOUR_MINUTE 3.2.3+ DAY_MICROSECOND 4.1.1+ DAY_SECOND 3.2.3+ DAY_MINUTE 3.2.3+ DAY_HOUR 3.2.3+ YEAR_MONTH 3.2.3+ Note If you specify an interval value that is too short for the unit that you have specified, the DATE_SUB function will assume that the left-most portion of the interval value was not provided. Using the DATE_SUB function with a negative value as a parameter is equivalent to using the DATE_ADD function. See also the DATE_ADD, ADDDATE, SUBDATE, ADDTIME, and SUBTIME functions. Applies To

The DATE_SUB function can be used in the following versions of MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23 Example

Let's look at some MySQL DATE_SUB function examples and explore how to use the DATE_SUB function in MySQL.

For example:

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL 4 MICROSECOND); Result: '2014-02-13 08:44:20.999997' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 20 SECOND); Result: '2014-02-13 08:44:01' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 25 MINUTE); Result: '2014-02-13 08:19:21' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 2 HOUR); Result: '2014-02-13 06:44:21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 10 DAY); Result: '2014-02-03' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 12 WEEK); Result: '2013-11-21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 MONTH); Result: '2013-11-13' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 QUARTER); Result: '2013-05-13' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 5 YEAR); Result: '2009-02-13' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND); Result: '2014-02-13 08:44:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND); Result: '2014-02-13 08:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND); Result: '2014-02-13 08:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND); Result: '2014-02-13 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND); Result: '2014-02-13 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE); Result: '2014-02-13 07:41:21' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND); Result: '2014-02-06 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND); Result: '2014-02-06 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE); Result: '2014-02-06 07:41:21' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR); Result: '2014-02-06 07:44:21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL '5-3' YEAR_MONTH); Result: '2008-11-13'

【本文地址】

公司简介

联系我们