4. Date and time

MySQL supports various formats for dates and time. In this chapter, these different date-time formats are discussed along with the conversion and arithmetic operations. We will use the same table which is created in Section More on SELECT statement.

4.1. Current date and time

Current date and time functions can use useful to fill the fields automatically while inserting or updating the data. NOW(), CURDATE() and CURTIME() functions can be used for this purpose as shown below,

mysql> SELECT NOW(), CURDATE(), CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2017-03-02 13:59:27 | 2017-03-02 | 13:59:27  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)

4.2. Decomposing dates and times

In this section, we will see various ways to extract the date and times from a date-time field.

4.2.1. Using formatting functions

DATE_FORMAT and TIME_FORMAT functions can be used to extract certain part from the date-time filed. In the following code, DATE_FORMAT is used. Note that, TIME_FORMAT can be used only if the field contains the time as well; whereas DATE_FORMAT can be used in all the cases.

mysql> SELECT
    -> t,
    -> DATE_FORMAT(t, '%M %d, %Y') as date, # change format of t
    -> srcuser
    -> FROM mail LIMIT 3;

+---------------------+--------------+---------+
| t                   | date         | srcuser |
+---------------------+--------------+---------+
| 2014-05-11 10:15:08 | May 11, 2014 | barb    |
| 2014-05-12 12:48:13 | May 12, 2014 | tricia  |
| 2014-05-12 15:02:49 | May 12, 2014 | phil    |
+---------------------+--------------+---------+
3 rows in set (0.00 sec)


mysql> SELECT t, TIME_FORMAT(t, '%H') as date,  srcuser FROM mail LIMIT 3;
+---------------------+------+---------+
| t                   | date | srcuser |
+---------------------+------+---------+
| 2014-05-11 10:15:08 | 10   | barb    |
| 2014-05-12 12:48:13 | 12   | tricia  |
| 2014-05-12 15:02:49 | 15   | phil    |
+---------------------+------+---------+
  • Commonly used formats are shown in below table,
Format Description
%Y YYYY
%y YY
%M complete name of month e.g. July
%m month in two digit format i.e. 01
%c month of year i.e. 1, 2 etc.
%d two digit date i.e. 01
%e day of month i.e. 1, 2
%r 12 Hr time with AM/PM
%T 24 Hr time
%H two digit hour
%i two digit minute
%s two digit second

4.2.2. Using component extraction function

There are various functions available in MySQL, which can be used to extract the fields e.g. YEAR() and MONTH() etc.

mysql> SELECT t,
    -> YEAR(t) as year,   # year ot t
    -> SECOND(t) as sec   # second of t
    -> from mail LIMIT 3;
+---------------------+------+------+
| t                   | year | sec  |
+---------------------+------+------+
| 2014-05-11 10:15:08 | 2014 |    8 |
| 2014-05-11 10:15:08 | 2014 |    8 |
| 2014-05-12 12:48:13 | 2014 |   13 |
+---------------------+------+------+
Table 4.1 Component extraction function
Function Description
YEAR() year
MONTH() month number 1,2
MONTHNAME() name of month
DAYOFMONTH() day of the month 1,2
DAYNAME() sunday, monday
DAYOFWEEK() 1..7 for Sun..Sat
WEEKDAY() 0..6 for Mon..Sun
DAYOFYEAR() 1..366
HOUR() 0..23
MINUTEi() 0..59
SECOND() 0..59
  • Saving Sunday as ‘Sun’ etc.
mysql> SELECT t,
    -> LEFT(DAYNAME(t), 3)
    -> FROM mail LIMIT 3;
+---------------------+---------------------+
| t                   | LEFT(DAYNAME(t), 3) |
+---------------------+---------------------+
| 2014-05-11 10:15:08 | Sun                 |
| 2014-05-11 10:15:08 | Sun                 |
| 2014-05-12 12:48:13 | Mon                 |
+---------------------+---------------------+

4.3. Calculating intervals

4.3.1. Interval between times

‘TIME_TO_SEC’ and ‘SEC_TO_TIME’ functions are used to convert the time into seconds and second into time respectively. With the help of these functions, we can calculate the time interval as shown below.

  • Note that the difference is between time only (dates are not considered in the difference).
mysql> SELECT
    -> TIME_TO_SEC(NOW())-TIME_TO_SEC(t) as 'interval in sec'
    -> FROM mail LIMIT 3;
+-----------------+
| interval in sec |
+-----------------+
|           16901 |
|           16901 |
|            7716 |
+-----------------+
  • To convert the above result into HOUR, MIN and SEC, we can use ‘extraction functions’ as shown below,
mysql> SELECT
    -> @sec := TIME_TO_SEC(NOW())-TIME_TO_SEC(t) as 'interval in sec',
    -> HOUR(SEC_TO_TIME(@sec)) AS 'hour',
    -> MINUTE(SEC_TO_TIME(@sec)) AS 'minute',
    -> SECOND(SEC_TO_TIME(@sec)) AS 'second'
    -> FROM mail LIMIT 3;
+-----------------+------+--------+--------+
| interval in sec | hour | minute | second |
+-----------------+------+--------+--------+
|           17298 |    4 |     48 |     18 | # 4*3600 + 48*60 + 18 = 17298
|           17298 |    4 |     48 |     18 |
|            8113 |    2 |     15 |     13 |
+-----------------+------+--------+--------+

4.3.2. Interval between days

TO_DAYS can be used to calculate total interval in terms of dates as shown below,

mysql> SELECT
    -> @day := TO_DAYS(NOW())-TO_DAYS(t) as 'Total days'
    -> FROM mail LIMIT 3;
+------------+
| Total days |
+------------+
|       1026 |
|       1026 |
|       1025 |
+------------+
3 rows in set (0.00 sec)
  • Days can be conveted into Year and months etc. as below,
mysql> SELECT @day := TO_DAYS(NOW())-TO_DAYS(t) as 'Total days',
    -> MONTH(@day) AS 'Month',
    -> DAYOFYEAR(@day) AS 'Days'
    -> FROM mail LIMIT 3;
+------------+-------+------+
| Total days | Month | Days |
+------------+-------+------+
|       1026 |    10 |  300 |
|       1026 |    10 |  300 |
|       1025 |    10 |  299 |
+------------+-------+------+

4.3.3. Adding time to dates

DATE_ADD can be used to shift the dates by a fixed amount,

mysql> SELECT t,
    -> DATE_ADD(t, INTERVAL 7 DAY) AS '1 week',
    -> DATE_ADD(t, INTERVAL 2 MONTH) AS '2 month'
    -> FROM mail LIMIT 3;
+---------------------+---------------------+---------------------+
| t                   | 1 week              | 2 month             |
+---------------------+---------------------+---------------------+
| 2014-05-11 10:15:08 | 2014-05-18 10:15:08 | 2014-07-11 10:15:08 |
| 2014-05-11 10:15:08 | 2014-05-18 10:15:08 | 2014-07-11 10:15:08 |
| 2014-05-12 12:48:13 | 2014-05-19 12:48:13 | 2014-07-12 12:48:13 |
+---------------------+---------------------+---------------------+