MySQL Note 5 Date and Time Function

1. Functions to get the current date and the current time

mysql> SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0;
+------------+----------------+-------------+
| CURDATE()  | CURRENT_DATE() | CURDATE()+0 |
+------------+----------------+-------------+
| 2017-02-06 | 2017-02-06     |    20170206 |
+------------+----------------+-------------+
1 row in set (0.04 sec)

mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0;
+-----------+----------------+-------------+
| CURTIME() | CURRENT_TIME() | CURTIME()+0 |
+-----------+----------------+-------------+
| 19:42:14  | 19:42:14       |      194214 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

Conclusion:
1. The CURDATE () and CURENT_DATE () functions work the same way. The current date is returned according to the value in YYYY-MM-DD or Y YYYYMMDD format, depending on whether the function is used in a string or in a digital context.
2. The CCURTIME () and CURENT_TIME () functions work the same way. The current date is returned according to the value of the `HH:MM:SS'or HHMMSS format, depending on whether the function is used in a string or in a digital context.

2. Functions to get the current date and time

mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2017-02-06 19:51:12 | 2017-02-06 19:51:12 | 2017-02-06 19:51:12 | 2017-02-06 19:51:12 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Conclusion:
CURRENT_TIMESTAMP(), LOCALTIME (), NOW (), SYSDATE () all return the current date and time values. The format is YYYYY-MM-DD HH: MM: SS or YYYYMMDHMMSS, depending on the current function in the string or digital context.

3. Get UNXI timestamp function

mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1486382315 |            1486382315 | 2017-02-06 19:58:35 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME('1486382315');
+-----------------------------+
| FROM_UNIXTIME('1486382315') |
+-----------------------------+
| 2017-02-06 19:58:35         |
+-----------------------------+
1 row in set (0.00 sec)

Conclusion:
1. The time when the UNIX timestamp of the FROM_UNIXTIME (date) function is converted to the normal format is inverse to that of the UNIX_TIMESTAMP(date) function.

4. Functions that return UTC date and UTC time

mysql> SELECT UTC_DATE(),UTC_DATE()+0;
+------------+--------------+
| UTC_DATE() | UTC_DATE()+0 |
+------------+--------------+
| 2017-02-06 |     20170206 |
+------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT UTC_TIME(),UTC_TIME()+0;
+------------+--------------+
| UTC_TIME() | UTC_TIME()+0 |
+------------+--------------+
| 12:17:59   |       121759 |
+------------+--------------+
1 row in set (0.00 sec)

Conclusion:
1. The UTC_DATE() function returns the current UTC (World Standard Time) date value in the format YYYY-MM-DD or YY YYYYMMDD, depending on whether the function is used in a string or digital context.
2. The UTC_TIME() function returns the current UTC (world standard time) time value in the form of `HH:MM:SS'or HHMMSS, depending on whether the function is used in a string or in a digital context.

5. Get month functions MONTH(date) and MONTTHNAME (date)

mysql>  SELECT MONTH('2017-02-06');
+---------------------+
| MONTH('2017-02-06') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME('2017-02-06');
+-------------------------+
| MONTHNAME('2017-02-06') |
+-------------------------+
| February                |
+-------------------------+
1 row in set (0.00 sec)

Conclusion:
1. The MONTH (date) function returns the date corresponding month, ranging from 1 to 12.
2. The MONTHNAME (date) function returns the full English name of the month corresponding to date.

6. Get weekly functions DAYNAME(date), DAYOFWEEK(date) and WEEKDAY(date)

mysql> SELECT DAYNAME('2017-02-06');
+-----------------------+
| DAYNAME('2017-02-06') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec) 

mysql> SELECT DAYOFWEEK('2017-02-06');
+-------------------------+
| DAYOFWEEK('2017-02-06') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)  


mysql> SELECT WEEKDAY('2017-02-06'),WEEKDAY('2017-02-06 19:58:35 ');
+-----------------------+---------------------------------+
| WEEKDAY('2017-02-06') | WEEKDAY('2017-02-06 19:58:35 ') |
+-----------------------+---------------------------------+
|                     0 |                               0 |
+-----------------------+---------------------------------+
1 row in set (0.00 sec)

Conclusion:
1. The DAYNAME (date) function returns the English name of the corresponding working day for date.
2.DAYOFWEEK(date) returns the index (location) of the date corresponding to the week. 1 means Sunday, 2 means Monday,... 7 means Saturday.
3.WEEKDAY(date) returns the date's corresponding workday index. 0 means Monday, 1 means Tuesday,... 6 means Sunday.
4. Both the DAYOFWEEK () and WEEKDAY() functions return the location of the specified date within a week, except that the index number is different.

7. Get Weeks WEEK(d) and WEEKOFYEAR(d)

mysql> SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0),WEEK('2011-02-20',1);
+--------------------+----------------------+----------------------+
| WEEK('2011-02-20') | WEEK('2011-02-20',0) | WEEK('2011-02-20',1) |
+--------------------+----------------------+----------------------+
|                  8 |                    8 |                    7 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2011-02-20',3),WEEKOFYEAR('2011-02-20');
+----------------------+--------------------------+
| WEEK('2011-02-20',3) | WEEKOFYEAR('2011-02-20') |
+----------------------+--------------------------+
|                    7 |                        7 |
+----------------------+--------------------------+
1 row in set (0.00 sec)

Conclusion:
1. The WEEK () function queries the specified date for the week of the year
2.WEEK('2011-02-20') uses a parameter whose second parameter is default_week_format default value, which is 0. It specifies that the first day of a week is Sunday, so it returns the same result as WEEK('2011-02-20', 0). The second parameter is 1, specifying the first day of the week as Monday, with a return value of 7.
3.WEEKOFYEAR(d) calculates the weeks of a day in a year and returns from 1 to 53. That is equivalent to WEEK(d,3).

8. Get Weeks WEEK(d) and WEEKOFYEAR(d)

mysql>  SELECT DAYOFYEAR('2017-02-06');
+-------------------------+
| DAYOFYEAR('2017-02-06') |
+-------------------------+
|                      37 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH('2017-02-06');
+--------------------------+
| DAYOFMONTH('2017-02-06') |
+--------------------------+
|                        6 |
+--------------------------+
1 row in set (0.00 sec)

Conclusion:
1.DAYOFYEAR(d) returns D on the first day of the year, ranging from 1 to 366;
2.DAYOFMONTH(d) return D is the day of a month, ranging from 1 to 31;

9. Functions to get years, quarters, hours, minutes and seconds

mysql> SELECT YEAR('17-02-06'),YEAR('96-02-06');
+------------------+------------------+
| YEAR('17-02-06') | YEAR('96-02-06') |
+------------------+------------------+
|             2017 |             1996 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT QUARTER('17-02-06');
+---------------------+
| QUARTER('17-02-06') |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MINUTE('17-02-06 19:58:35');
+-----------------------------+
| MINUTE('17-02-06 19:58:35') |
+-----------------------------+
|                          58 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND('19:58:35');
+--------------------+
| SECOND('19:58:35') |
+--------------------+
|                 35 |
+--------------------+
1 row in set (0.00 sec)

Conclusion:
1.YEAR(date) returns the year corresponding to date, ranging from 1970 to 2069. ('00-69'converted to 2000-2069,'70-99' converted to 1970-1999)
2.QUARTER(date) returns the quarterly value of the year corresponding to date, ranging from the quarter corresponding to the specified date.
3.MINUTE(time) returns the number of minutes corresponding to time, ranging from 0 to 59.
4.SECOND(time) returns the number of seconds corresponding to time, ranging from 0 to 59.

10. Function EXTRACT(type FROM date) to get the specified value of the date

mysql> SELECT EXTRACT(YEAR FROM '2017-02-06') AS col1,
    -> EXTRACT(YEAR_MONTH FROM '2017-02-06 19:58:35') AS col2,
    -> EXTRACT(DAY_MINUTE FROM '2017-02-06 19:58:35') AS col3;
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
| 2017 | 201702 | 61958 |
+------+--------+-------+
1 row in set (0.07 sec)

Conclusion:
1.EXTRACT(type FROM date) function extracts part of the date.
2. When the type value is YEAR, only the annual value is returned. When type is YEAR_MONTH, the year and month are returned. When type is DAY_MINUTE, return the values of day, hour and minute.

11. Functions for Calculating Date and Time

mysql> SELECT DATE_ADD('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col1,
    -> ADDDATE('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col2,
    -> DATE_ADD('2017-02-06 19:58:35',INTERVAL '1:1' MINUTE_SECOND) AS col3;
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2017-02-06 19:58:36 | 2017-02-06 19:58:36 | 2017-02-06 19:59:36 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Conclusion:
1.DATE_ADD() and ADD DATE () function the same, and perform the date addition operation.

 mysql> SELECT DATE_SUB('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col1,
    -> SUBDATE('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col2,
    -> DATE_SUB('2017-02-06 19:58:35',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2017-02-06 19:58:34 | 2017-02-06 19:58:34 | 2017-02-06 19:57:34 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Conclusion:
1.DATE_SUB()SUBDATE() performs the same function and performs the date subtraction operation.
2.DATE_ADD and DATE_SUB can also be negative when specifying the time period of modification. Negative values represent subtraction.

mysql> SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'),ADDTIME('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| ADDTIME('2000-12-31 23:59:59','1:1:1') | ADDTIME('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2001-01-01 01:01:00                    | 04:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'),SUBTIME('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| SUBTIME('2000-12-31 23:59:59','1:1:1') | SUBTIME('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2000-12-31 22:58:58                    | 00:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

Summary: ADDTIME() and SUBTIME() are used to add and subtract time.

mysql> SELECT DATEDIFF('2017-02-06 19:58:35','2017-02-05') AS col1,
    -> DATEDIFF('2017-02-06 19:58:35','2017-03-05') AS col2;
+------+------+
| col1 | col2 |
+------+------+
|    1 |  -27 |
+------+------+
1 row in set (0.00 sec)

Summary: The DATEDIFF() function calculates the number of days between two dates.

12. Functions that format dates and times

mysql> SELECT DATE_FORMAT('2017-02-06 19:58:35','%W %M %Y') AS col1,
    -> DATE_FORMAT('2017-02-06 19:58:35','%D %y %a %d %m %b %j') AS col2;
+----------------------+--------------------------+
| col1                 | col2                     |
+----------------------+--------------------------+
| Monday February 2017 | 6th 17 Mon 06 02 Feb 037 |
+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql>  SELECT DATE_FORMAT('2017-02-06 19:58:35','%H:%i:%s') AS col1,
    ->  DATE_FORMAT('2017-02-06 19:58:35','%X %V') AS col2;
+----------+---------+
| col1     | col2    |
+----------+---------+
| 19:58:35 | 2017 06 |
+----------+---------+
1 row in set (0.00 sec)

mysql> SELECT TIME_FORMAT('19:58:35','%H %k %h %I %l');
+------------------------------------------+
| TIME_FORMAT('19:58:35','%H %k %h %I %l') |
+------------------------------------------+
| 19 19 07 07 7                            |
+------------------------------------------+
1 row in set (0.00 sec)

Summary: 1.DATE_FORMAT(date,format) displays date values in format-specified formats.

header 1	header 2
row 1 col 1	row 1 col 2
row 2 col 1	row 2 col 2

DATE_FORMAT Time and Date Format:

Specifier	Explain
%a	Abbreviated name of working day (Sun. Sat)
%b	Abbreviated name of month (Jan. Dec)
%c	Month, numeric form (0.12)
%D	Dates of the month with English suffixes (0th, 1st, 2nd, 3rd,... )
%d	Date of the month, in digital form (00.31)
%e	Date of the month, in digital form (0.31)
%f	Microseconds (000000. 999999)
%H	Hours (00.23)
%h	Hours (01.12)
%I	Hours (01.12)
%i	Minutes, in digital form (00.59)
%j	Days in a year (001.366)
%k	Hours (0.23)
%l	Hours (1.12)
%M	Month name (January. December)
%m	Month, numeric form (00.12)
%p	Morning (AM) or afternoon (PM)
%r	Time, 12-hour system (hour hh: minute mm: seconds ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour system (hh: minutes mm: seconds ss)
%U	Week (00.53), with Sunday as the first day of the week
%u	Week (00.53), Monday being the first day of the week
%V	Week (01.53), with Sunday as the first day of the week; and% X used simultaneously
%v	Week (01.53), Monday is the first day of the week; and% x is used together.
%W	Name of working day (Sunday... Saturday)
%w	Daily of the week (0 = Sunday... 6 = Saturday)
%X	The year of the week, in which Sunday is the first day of the week, in numerical form, 4 digits; and% V are used simultaneously.
%x	For the year of the week, Monday is the first day of the week, in numeric form, with four digits; and% v is used simultaneously.
%Y	Year, number form, 4 digits
%y	Year, number form (2 digits)
%%	'%'character

mysql> SELECT GET_FORMAT(DATE,'EUR'),GET_FORMAT(DATE,'USA');
+------------------------+------------------------+
| GET_FORMAT(DATE,'EUR') | GET_FORMAT(DATE,'USA') |
+------------------------+------------------------+
| %d.%m.%Y               | %m.%d.%Y               |
+------------------------+------------------------+
1 row in set (0.00 sec)

Summary: The GET_FORMAT() function displays format strings of different formatting types.

The format string returned by GET_FORMAT:

function	Format string returned	Examples of dates and times
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'	30.03.2014
GET_FORMAT(DATE,'USA')	'$m.%d.%Y'	03.30.2014
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'	2014-03-30
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'	2014-03-30
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'	20140330
GET_FORMAT(DATETIME,'EUR')	'%Y-%m-%d-%H.%i.%s'	2014-03-30-22.48.08
GET_FORMAT(DATETIME,'USA')	'%Y-%m-%d-%H.%i.%s'	2014-03-30-22.48.08
GET_FORMAT(DATETIME,'JIS')	'%Y-%m-%d %H:%i:%s'	2014-03-30 22:48:08
GET_FORMAT(DATETIME,'ISO')	'%Y-%m-%d %H:%i:%s'	2014-03-30 22:48:08
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%H%i%s'	20140330224808
GET_FORMAT(TIME,'EUR')	'%H.%i.%s'	22.48.08
GET_FORMAT(TIME,'USA')	'%h:%i:%s %p'	10:48:08 PM
GET_FORMAT(TIME,'JIS')	'%H:%i:%s'	22:48:08
GET_FORMAT(TIME,'ISO')	'%H:%i:%s'	22:48:08
GET_FORMAT(TIME,'INTERNAL')	'%H%i%s'	224808

In the DATE_FORMAT() function, the display format string returned by the GET_FORMAT() function is used to display the specified date.

mysql> SELECT DATE_FORMAT('2017-02-06 19:58:35',GET_FORMAT(DATE,'USA'));
+-----------------------------------------------------------+
| DATE_FORMAT('2017-02-06 19:58:35',GET_FORMAT(DATE,'USA')) |
+-----------------------------------------------------------+
| 02.06.2017                                                |
+-----------------------------------------------------------+
1 row in set (0.00 sec)