CODEWITHSUNDEEP
mysqlsqldatesql-update
Chevy Mark Sunderland
Chevy Mark Sunderland

Reputation: 435

Usage the ADDTIME() function in MySQL

I need using on MySQL the ADDTIME() function for adds a time interval to a time/datetime and then returns the time/datetime.

This is my MySQL table

+-----+---------------------+----------+-----+
| m   | mDate               | mDatenew | sID |
+-----+---------------------+----------+-----+
| 182 | 2020-06-04 20:15:00 | NULL     |   1 |
| 173 | 2020-06-13 21:35:13 | NULL     |   2 |
|  74 | 2020-06-13 16:22:50 | NULL     |   3 |
+-----+---------------------+----------+-----+

I need update the values of columns mDatenew with the value of colum mDate adding the value in minute of column m

First the update I have tried this query for check values but the return is null

mysql> SELECT
    DATE_FORMAT(
        ADDTIME(mDate, m),
        '%Y-%m-%d %H:%i:%s'
    ) AS t
FROM
    `n`;
+------+
| t    |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set

How to do resolve this?

My table below

DROP TABLE IF EXISTS `n`;
CREATE TABLE `n` (
  `m` int(11) DEFAULT NULL,
  `mDate` datetime DEFAULT NULL,
  `mDatenew` datetime DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of n
-- ----------------------------
INSERT INTO `n` VALUES ('182', '2020-06-04 20:15:00', null, '1');
INSERT INTO `n` VALUES ('173', '2020-06-13 21:35:13', null, '2');
INSERT INTO `n` VALUES ('74', '2020-06-13 16:22:50', null, '3');

Upvotes: 1

Views: 382

Answers (1)

GMB
GMB

Reputation: 222482

I need update the values of columns mDatenew with the value of colum mDate adding the value in minute of column m.

MySQL understands date arithmetics with intervals. You can increment the date as follows:

mDate + interval m minute

If you want an update statement:

update n set mDatenew = mDate + interval m minute

Upvotes: 1

Related Questions