MySQL ADDDATE () Function
ADDDATE(date, INTERVAL value addunit)
ADDDATE(date, days)
The ADDDATE() function adds a time/date interval to a date and then returns the date.
date Required. The date to be modified
days Required. The number of days to add to date
value Required. The value of the time/date interval to add. Both positive and negative values are allowed
addunit Required. The type of interval to add. Can be one of the following values:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
SELECT ADDDATE("2021-06-03", INTERVAL 10 DAY);
SELECT ADDDATE("2021-06-03 09:34:21", INTERVAL 15 MINUTE);
-------------------
MySQL ADDTIME() Function
ADDTIME(datetime, addtime)
SELECT ADDTIME("2017-06-15 09:34:21", "2");
Example
Add 5 seconds and 3 microseconds to a time and return the datetime:
SELECT ADDTIME("2017-06-15 09:34:21.000001", "5.000003");
Example
Add 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the datetime:
SELECT ADDTIME("2017-06-15 09:34:21.000001", "2:10:5.000003");
Example
Add 5 days, 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the datetime:
SELECT ADDTIME("2017-06-15 09:34:21.000001", "5 2:10:5.000003");
Example
Add 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the time:
SELECT ADDTIME("09:34:21.000001", "2:10:5.000003");
================================
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst;
Database changed
MariaDB [vlrinst]> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| classicmodels |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| vlrinst |
+--------------------+
7 rows in set (0.108 sec)
MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| dt |
| emp |
| emp1 |
| people |
| ts |
| vlr |
+-------------------+
6 rows in set (0.001 sec)
MariaDB [vlrinst]> select * from vlr;
+-------------------------------------+---------------------+---------------------+
| comment | created_at | changed_at |
+-------------------------------------+---------------------+---------------------+
| Please save my number +919059868766 | 2021-05-26 22:32:47 | 2021-05-26 22:36:50 |
| please subcribe vlr | 2021-05-26 22:36:01 | 2021-05-26 22:36:01 |
+-------------------------------------+---------------------+---------------------+
2 rows in set (0.160 sec)
MariaDB [vlrinst]> select adddate("2021-06-03", interval 10 day);
+----------------------------------------+
| adddate("2021-06-03", interval 10 day) |
+----------------------------------------+
| 2021-06-13 |
+----------------------------------------+
1 row in set (0.096 sec)
MariaDB [vlrinst]> select created_at,adddate(created_at,interval 20 day) from vlr;
+---------------------+-------------------------------------+
| created_at | adddate(created_at,interval 20 day) |
+---------------------+-------------------------------------+
| 2021-05-26 22:32:47 | 2021-06-15 22:32:47 |
| 2021-05-26 22:36:01 | 2021-06-15 22:36:01 |
+---------------------+-------------------------------------+
2 rows in set (0.029 sec)
MariaDB [vlrinst]> select created_at,adddate(created_at,30) from vlr;
+---------------------+------------------------+
| created_at | adddate(created_at,30) |
+---------------------+------------------------+
| 2021-05-26 22:32:47 | 2021-06-25 22:32:47 |
| 2021-05-26 22:36:01 | 2021-06-25 22:36:01 |
+---------------------+------------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select created_at,adddate(created_at,-30) from vlr;
+---------------------+-------------------------+
| created_at | adddate(created_at,-30) |
+---------------------+-------------------------+
| 2021-05-26 22:32:47 | 2021-04-26 22:32:47 |
| 2021-05-26 22:36:01 | 2021-04-26 22:36:01 |
+---------------------+-------------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select created_at,adddate(created_at,interval 20 hour) from vlr;
+---------------------+--------------------------------------+
| created_at | adddate(created_at,interval 20 hour) |
+---------------------+--------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 18:32:47 |
| 2021-05-26 22:36:01 | 2021-05-27 18:36:01 |
+---------------------+--------------------------------------+
2 rows in set (0.002 sec)
MariaDB [vlrinst]> select created_at,adddate(created_at,interval 2 year) from vlr;
+---------------------+-------------------------------------+
| created_at | adddate(created_at,interval 2 year) |
+---------------------+-------------------------------------+
| 2021-05-26 22:32:47 | 2023-05-26 22:32:47 |
| 2021-05-26 22:36:01 | 2023-05-26 22:36:01 |
+---------------------+-------------------------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> SELECT ADDTIME("2021-06-03 09:34:21", "2");
+-------------------------------------+
| ADDTIME("2021-06-03 09:34:21", "2") |
+-------------------------------------+
| 2021-06-03 09:34:23 |
+-------------------------------------+
1 row in set (0.018 sec)
MariaDB [vlrinst]> select created_at,addtime(created_at,2:10:30) from vlr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':10:30) from vlr' at line 1
MariaDB [vlrinst]> select created_at,addtime(created_at,"2:10:30") from vlr;
+---------------------+-------------------------------+
| created_at | addtime(created_at,"2:10:30") |
+---------------------+-------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 00:43:17 |
| 2021-05-26 22:36:01 | 2021-05-27 00:46:31 |
+---------------------+-------------------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select created_at,addtime(created_at,"2:10:30.0000020") from vlr;
+---------------------+---------------------------------------+
| created_at | addtime(created_at,"2:10:30.0000020") |
+---------------------+---------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 00:43:17.000002 |
| 2021-05-26 22:36:01 | 2021-05-27 00:46:31.000002 |
+---------------------+---------------------------------------+
2 rows in set, 2 warnings (0.015 sec)
MariaDB [vlrinst]> select created_at,addtime(created_at,"3 1:10:30.0000025") from vlr;
+---------------------+-----------------------------------------+
| created_at | addtime(created_at,"3 1:10:30.0000025") |
+---------------------+-----------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-29 23:43:17.000002 |
| 2021-05-26 22:36:01 | 2021-05-29 23:46:31.000002 |
+---------------------+-----------------------------------------+
2 rows in set, 2 warnings (0.000 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.