Soothingday
0%

The Common Date Function in MySQL

Posted on Edited on In ,

In MySQL, there are many situations that we need to use date functions, not only the transformation from timestamp to date, but also the comparision between different dates. Therefore, it is necessary for me to records some common date functions and consolidate my knowledge in this apsect.

Before starting this article, I would like to say some differences among three general time type in MySQL, date, datetime and timestamp.

  1. The format of date type is “YYYY-MM-DD”, so the date type can be accurate to ‘date’. In contrast, datetime type and timestamp type can be accurate to second.
  2. The range that timestamp covers is from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. And the range for datetime type is more larger than for timestamp. It can express the time from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
  3. The time in timestamp can be converted automatically based on the timezone of the database, because in database it stores as UTC time zone, and when getting it from database, it will be converted based on UTC. The time in datetime is stable and cannot change as the time zone changes.
  4. Timestamp needs to use 4 bytes to store, datetime needs 8 bytes, and date uses 3 bytes.

1. Get the current time

1.1 CURDATE() / CURRENT_DATE() / CURRENT_DATE

Get the current date in system 

1
2
3
4
select CURDATE()

CURDATE()
2021-02-16

1.2 CURTIME() / CURRENT_TIME() / CURRENT_TIME

Get the current time in system（hh:mm:ss). 

1
2
3
4
select CURTIME()

CURTIME()
14:22:16

1.3 NOW() / CURRENT_TIMESTAMP() / CURRENT_TIMESTAMP

Get the current time in system (YYYY-MM-DD hh:mm:ss) 

1
2
3
4
select NOW(), CURRENT_TIMESTAMP

NOW()                 CURRENT_TIMESTAMP
2021-02-16 14:26:47   2021-02-16 14:26:47

1.4 SYSDATE()

Get the current time in system (YYYY-MM-DD hh:mm:ss) 

1
2
3
4
select SYSDATE()

SYSDATE()           
2021-02-16 14:27:57

The difference between SYSDATE() and NOW()： NOW() records the time when the query begins to execute，rather than SYSDATE() records the exact time that MySQL executes the SYSDATE() fucntion.

The following is the example on MySQL official document to explain the difference between NOW() and SYSDATE(). 

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

2. ADD/SUB Date

2.1 ADDDATE

There are two usages in this function，ADDDATE(date,INTERVAL expr unit) and ADDDATE(expr,days)
The first usage for this function is the synonym of DATE_ADD(date,INTERVAL expr unit) function.
INTERVAL expr unit means that the time what you will add based on the first date parameter. For instance, INTERVAL 6 HOUR means that add 6 hours and INTERVAL -2 day means that subtract 2 days.（The meaning for interval is reverse when using SUBDATE() fucntion）

Example: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select date_add("2021-02-16 14:00:00", interval 5 hour), 
date_add("2021-02-16 14:00:00", interval 1 day),
date_add("2021-02-16 14:00:00", interval -2 day),
adddate("2021-02-16 14:00:00", INTERVAL '1 1:1:1' DAY_SECOND),
adddate("2021-02-16 14:00:00",3) ,
adddate("2021-02-16 14:00:00",-4)

Output:
2021-02-16 19:00:00	 
2021-02-17 14:00:00	 
2021-02-14 14:00:00	 
2021-02-17 15:01:01	 
2021-02-19 14:00:00	 
2021-02-12 14:00:00

2.2 ADDTIME

The usage for this function

ADDTIME(expr1,expr2)

expr1 is the date/time format, and the expr2 is the time format

The example on MySQL document 

1
2
3
4
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

2.3 SUBDATE

There are two usages in this function，SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)
The first usage for this function is the synonym of DATE_SUB(date,INTERVAL expr unit) function. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select date_sub("2021-02-16 14:00:00", interval 5 hour), 
date_sub("2021-02-16 14:00:00", interval 1 day),
date_sub("2021-02-16 14:00:00", interval -2 day),
subdate("2021-02-16 14:00:00", INTERVAL '1 1:1:1' DAY_SECOND),
subdate("2021-02-16 14:00:00",3) ,
subdate("2021-02-16 14:00:00",-4)

Output
2021-02-16 09:00:00	 
2021-02-15 14:00:00	 
2021-02-18 14:00:00	 
2021-02-15 12:58:59	  
2021-02-13 14:00:00	  
2021-02-20 14:00:04

2.4 SUBTIME

The usage for this function

SUBTIME(expr1,expr2)

Similar to ADDTIME, difference is that one does the addition, one does the subtraction.

2.5 DATEDIFF

The usage for this function, mainly to calculate the date difference between two times.

DATEDIFF(expr1,expr2)

The formats for expr1 and expr2 are date format or date+time format. Time part will not be considered in the calculation, only calculate the date part. 

1
2
3
4
5
6
7
8
select datediff("2021-02-16 14:00:00", "2021-02-15 12:00:00"),
datediff("2021-02-16 14:00:00", "2021-02-15 21:00:00"),  
datediff("2021-02-10", "2021-02-15 15:32:46")

Output
1
1
-5

2.6 TIMEDIFF

Similar to DATEDIFF, but this function is to calculate the time difference between two times. (can be accurate to millisecond)

TIMEDIFF(expr1,expr2)
Example: 

1
2
3
4
5
6
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'