MySQL日期时间函数 ADDTIME() 用法
时间:2021-12-06 作者:匿名
MYSQL ADDTIME()函数用于将指定的时间间隔添加到日期时间中。
语法
ADDTIME(expr1,expr2)
expr1是表示日期时间或时间的表达式,expr2是表示要添加的时间。
示例
以下示例演示了ADDTIME()函数的用法
mysql> SELECT ADDTIME('10:40:32.88558', '06:04:01.222222'); +----------------------------------------------+ | ADDTIME('10:40:32.88558', '06:04:01.222222') | +----------------------------------------------+ | 16:44:34.107802 | +----------------------------------------------+ 1 row in set (0.00 sec)
另一个示例
mysql> SELECT ADDTIME('2018-05-23 05:40:32.88558', '06:04:01.222222'); +---------------------------------------------------------+ | ADDTIME('2018-05-23 05:40:32.88558', '06:04:01.222222') | +---------------------------------------------------------+ | 2018-05-23 11:44:34.107802 | +---------------------------------------------------------+ 1 row 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');
以下查询将时间间隔添加到名为 DispatchTime 的列中的值:
mysql> SELECT ProductName, CustomerName, DispatchDate, DispatchTime, Price, ADDTIME(DispatchTime,'12:45:50') FROM Sales; +-------------+--------------+--------------+--------------+-------+----------------------------------+ | ProductName | CustomerName | DispatchDate | DispatchTime | Price | ADDTIME(DispatchTime,'12:45:50') | +-------------+--------------+--------------+--------------+-------+----------------------------------+ | Key-Board | Raja | 2019-09-01 | 11:00:00 | 7000 | 23:45:50 | | Earphones | Roja | 2019-05-01 | 11:00:00 | 2000 | 23:45:50 | | Mouse | Puja | 2019-03-01 | 10:59:59 | 3000 | 23:45:49 | | Mobile | Vanaja | 2019-03-01 | 10:10:52 | 9000 | 22:56:42 | | Headset | Jalaja | 2019-04-06 | 11:08:59 | 6000 | 23:54:49 | +-------------+--------------+--------------+--------------+-------+----------------------------------+ 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');
以下查询将时间间隔添加到名为 DispatchTimeStamp 的列中:
mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, ADDTIME(DispatchTimeStamp, '08:25:46') FROM dispatches_data; +-------------+--------------+---------------------+-------+----------------------------------------+ | ProductName | CustomerName | DispatchTimeStamp | Price | ADDTIME(DispatchTimeStamp, '08:25:46') | +-------------+--------------+---------------------+-------+----------------------------------------+ | Key-Board | Raja | 2019-05-04 15:02:45 | 7000 | 2019-05-04 23:28:31 | | Earphones | Roja | 2019-06-26 14:13:12 | 2000 | 2019-06-26 22:38:58 | | Mouse | Puja | 2019-12-07 07:50:37 | 3000 | 2019-12-07 16:16:23 | | Mobile | Vanaja | 2018-03-21 16:00:45 | 9000 | 2018-03-22 00:26:31 | | Headset | Jalaja | 2018-12-30 10:49:27 | 6000 | 2018-12-30 19:15:13 | +-------------+--------------+---------------------+-------+----------------------------------------+ 5 rows in set (0.21 sec)