MySQL日期时间函数 ADDDATE() 用法
MySQL 中的 DATE、DATETIME 和 TIMESTAMP 数据类型分别用于存储日期、日期和时间、时间戳值。时间戳是一个数值,记录了'1970年1月1日零时'到当前时间的毫秒数。
MYSQL ADDDATE()函数用于将指定的时间间隔添加到日期值,例如2021-12-05,加5天,ADDDATE函数就会返回2021-12-10。
语法
ADDDATE(date, INTERVAL expr unit);
参数说明
date是表示日期的值,它可以是 String、DATE(YEAR、MONTH 和 DAY)、DATETIME(HOURS、MINUTES 或 SECONDS)或 TIMESTAMP 类型。
expr是表示区间值的值。
unit是 expr 值表示的区间类型,可以是 DAY、WEEK、MONTH、QUARTER、YEAR、HOUR、MINUTE、SECOND、MICROSECOND。
还有一种写法是
ADDDATE(expr, days);
参数说明
expr - 是表示日期的表达式。
days - 是表示要添加到给定日期的天数的间隔。
示例
mysql> SELECT ADDDATE('2021-12-05', INTERVAL 10 DAY); +-----------------------------------------+ | ADDDATE('2021-12-05', INTERVAL 10 DAY) | +-----------------------------------------+ | 2021-12-15 | +-----------------------------------------+ 1 row in set (0.00 sec)
上面的示例用于给日期2021-12-05加上10天。
mysql> SELECT ADDDATE('2021-01-02', INTERVAL 4 YEAR); +-----------------------------------------+ | ADDDATE('2021-01-02', INTERVAL 4 YEAR) | +-----------------------------------------+ | 2025-01-02 | +-----------------------------------------+ 1 row in set (0.00 sec)
上面的示例给日期2021-01-02加上4年。
mysql> SELECT ADDDATE('2021-11-23 10:40:32', '13 6:4:1'); +---------------------------------------------------------+ | ADDDATE('2021-11-23 10:40:32.88558', '13 6:4:1') | +---------------------------------------------------------+ | 2021-12-06 10:40:32 | +---------------------------------------------------------+ 1 row in set, 1 warning (0.01 sec)
上面的示例给日期加上了13天又6小时4分1秒。
另一种语法的示例
以下示例演示了此函数的第二种语法,其中我们将要添加的天数作为间隔传递
mysql> SELECT ADDDATE('1995-11-15', 554); +----------------------------+ | ADDDATE('1995-11-15', 554) | +----------------------------+ | 1997-05-22 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('1995-11-15', 54); +---------------------------+ | ADDDATE('1995-11-15', 54) | +---------------------------+ | 1996-01-08 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('1995-11-15', 1154); +-----------------------------+ | ADDDATE('1995-11-15', 1154) | +-----------------------------+ | 1999-01-12 | +-----------------------------+ 1 row in set (0.00 sec)
我们也可以将负值作为参数传递给这个函数
mysql> SELECT ADDDATE('2015-09-05', INTERVAL -20 DAY); +-----------------------------------------+ | ADDDATE('2015-09-05', INTERVAL -20 DAY) | +-----------------------------------------+ | 2015-08-16 | +-----------------------------------------+ 1 row in set (0.10 sec) mysql> SELECT ADDDATE('1995-11-15', -554); +-----------------------------+ | ADDDATE('1995-11-15', -554) | +-----------------------------+ | 1994-05-10 | +-----------------------------+ 1 row in set (0.00 sec)
您还可以将列名作为参数传递给此函数。让我们使用 CREATE 语句在 MySQL 数据库中创建一个名为 MyPlayers 的表,如下所示 :
mysql> CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );
现在,我们将使用 INSERT 语句在 MyPlayers 表中插入 7 条记录 -
mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');
以下查询将 14 年添加到Date_Of_Birth列的实体-
mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, ADDDATE(Date_Of_Birth, INTERVAL 14 YEAR) FROM MyPlayers; +------------+------------+---------------+-------------+------------------------------------------+ | First_Name | Last_Name | Date_Of_Birth | Country | ADDDATE(Date_Of_Birth, INTERVAL 14 YEAR) | +------------+------------+---------------+-------------+------------------------------------------+ | Shikhar | Dhawan | 1981-12-05 | India | 1995-12-05 | | Jonathan | Trott | 1981-04-22 | SouthAfrica | 1995-04-22 | | Kumara | Sangakkara | 1977-10-27 | Srilanka | 1991-10-27 | | Virat | Kohli | 1988-11-05 | India | 2002-11-05 | | Rohit | Sharma | 1987-04-30 | India | 2001-04-30 | | Ravindra | Jadeja | 1988-12-06 | India | 2002-12-06 | | James | Anderson | 1982-06-30 | England | 1996-06-30 | +------------+------------+---------------+-------------+------------------------------------------+ 7 rows in set (0.00 sec)
让我们使用 CREATE 语句在 MySQL 数据库中创建另一个名为 Sales 的表,如下所示:
mysql> CREATE TABLE sales( ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DispatchTime time, Price INT, Location VARCHAR(255) ); Query OK, 0 rows affected (2.22 sec)
现在,我们将使用 INSERT 语句在Sales表中插入 5 条记录:
insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad'); insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam'); insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada'); insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai'); insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');
以下查询将 225 天添加到DispatchDate列的实体:
mysql> SELECT ProductName, CustomerName, DispatchDate, Price, ADDDATE(DispatchDate, 225) FROM sales; +-------------+--------------+--------------+-------+----------------------------+ | ProductName | CustomerName | DispatchDate | Price | ADDDATE(DispatchDate, 225) | +-------------+--------------+--------------+-------+----------------------------+ | Key-Board | Raja | 2019-09-01 | 7000 | 2020-04-13 | | Earphones | Roja | 2019-05-01 | 2000 | 2019-12-12 | | Mouse | Puja | 2019-03-01 | 3000 | 2019-10-12 | | Mobile | Vanaja | 2019-03-01 | 9000 | 2019-10-12 | | Headset | Jalaja | 2019-04-06 | 6000 | 2019-11-17 | +-------------+--------------+--------------+-------+----------------------------+ 5 rows in set (0.00 sec)
假设我们使用以下查询创建了一个名为 dispatches_data 的表,其中有 5 条记录:
mysql> CREATE TABLE dispatches_data( ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchTimeStamp timestamp, Price INT, Location VARCHAR(255) ); insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad'); insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam'); insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada'); insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai'); insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');
以下查询将 365 天添加到DispatchTimeStamp列的日期:
mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, ADDDATE(DispatchTimeStamp, 365) FROM dispatches_data; +-------------+--------------+---------------------+-------+---------------------------------+ | ProductName | CustomerName | DispatchTimeStamp | Price | ADDDATE(DispatchTimeStamp, 365) | +-------------+--------------+---------------------+-------+---------------------------------+ | Key-Board | Raja | 2019-05-04 15:02:45 | 7000 | 2020-05-03 15:02:45 | | Earphones | Roja | 2019-06-26 14:13:12 | 2000 | 2020-06-25 14:13:12 | | Mouse | Puja | 2019-12-07 07:50:37 | 3000 | 2020-12-06 07:50:37 | | Mobile | Vanaja | 2018-03-21 16:00:45 | 9000 | 2019-03-21 16:00:45 | | Headset | Jalaja | 2018-12-30 10:49:27 | 6000 | 2019-12-30 10:49:27 | +-------------+--------------+---------------------+-------+---------------------------------+ 5 rows in set (0.00 sec)
以下示例演示了 ADDDATE() 函数中各种可用单位的用法:
mysql> SELECT ADDDATE('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH ); +----------------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH ) | +----------------------------------------------------+ | 2026-09-22 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2021-03-22', INTERVAL '3' WEEK ); +-------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '3' WEEK ) | +-------------------------------------------+ | 2021-04-12 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2021-03-22', INTERVAL '3' QUARTER ); +----------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '3' QUARTER ) | +----------------------------------------------+ | 2021-12-22 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND); +--------------------------------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND) | +--------------------------------------------------------------------+ | 2021-03-22 00:10:11.362191 | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND); +-----------------------------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND) | +-----------------------------------------------------------------+ | 2021-03-22 00:00:12.362191 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2021-03-22', INTERVAL '10.21' MINUTE_SECOND); +-------------------------------------------------------+ | ADDDATE('2021-03-22', INTERVAL '10.21' MINUTE_SECOND) | +-------------------------------------------------------+ | 2021-03-22 00:10:21 | +-------------------------------------------------------+ 1 row in set (0.00 sec)