MySQL Common date addition and subtraction function and example tutorial

MySQL Common date addition and subtraction function and example tutorial
MySQL Date Type
MySQL Date Type: Date format, occupied storage space, date range comparison.
Date type storage space Date format date range
------------  ---------   --------------------- -----------------------------------------
DateTime 8 bytes Yyyy-mm-dd HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp 4 bytes Yyyy-mm-dd HH:MM:SS 1970-01-01 00:00:01 ~ 2038
Date 3 bytes Yyyy-mm-dd 1000-01-01 ~ 9999-12-31
Year 1 bytes YYYY 1901 ~ 2155
When you create a table in MySQL, it's easy to choose your own data type against the table above. But whether to choose a datetime or timestamp, may be a bit puzzled. The two date-time types have advantages: DateTime has a larger date range, and the timestamp occupies a smaller storage space, only half the DateTime.

In addition, columns of type timestamp have an attribute: By default, when inserts, update data, timestamp columns are automatically populated/updated at the current time (Current_timestamp). "Automatic" means that if you don't take care of it, MySQL will take care of it for you.

In general, I tend to use datetime date types.

MySQL Time Type: Time format, occupied storage space, time range.
Time Type         storage space       time format                   time range
------------ ------ ---  --------------------------------------------------------------
time            3 bytes   hh:mm:ss               -838:59:59          ~ 838:59:59
The time range is so large that it's a bit odd to have a negative value in particular. Later, read the MySQL manual to know that this is to meet the two date-time subtraction before this design.

Select Timediff (' 2000:01:31 23:59:59 ', ' 2000:01:01 00:00:00 '); --743:59:59
Select Timediff (' 2000:01:01 00:00:00 ', ' 2000:01:31 23:59:59 '); ---743:59:59
Select Timediff (' 23:59:59 ', ' 12:00:00 '); --11:59:59
Note that the two parameters of Timediff can only be datetime/timestamp, time type, and that the two parameter types are the same. namely: Datetime/timestamp and datetime/timestamp comparison;

Although the date-time type in MySQL is rich, unfortunately, these date-time types can only support up to the second level at present (2008-08-08) and do not support milliseconds or microseconds. There are no functions that produce milliseconds.

"Mysql:mysql Date data type, MySQL Time type usage Summary" applies to MySQL 5.X and above version.
First, MySQL get the current date time function
1.1 Get Current date + time (date + times) function: Now ()
Mysql> Select Now ();

+---------------------+
| Now () |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

In addition to the now () function to get the current date time, MySQL also has the following function:
Current_timestamp ()
, Current_timestamp
, LocalTime ()
, localtime
, Localtimestamp--(v4.0.6)
, Localtimestamp ()--(v4.0.6)

These date-time functions are equivalent to now (). Given that the now () function is short and easy to remember, it is always recommended to use now () instead of the functions listed above.
1.2 Get current date + time (date + times) function: Sysdate ()
The Sysdate () Date time function is similar to now (), except that the now () is obtained at the start value, and sysdate () gets the values dynamically when the function is executed. Take a look at the example below to see:
Mysql> Select Now (), Sleep (3), now ();

+---------------------+----------+---------------------+
| Now () | Sleep (3) | Now () |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+

Mysql> Select Sysdate (), Sleep (3), sysdate ();

+---------------------+----------+---------------------+
| Sysdate () | Sleep (3) | Sysdate () |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+

As you can see, the time value of the now () function two times is the same, although it is 3 seconds Midway, the sysdate () function gets a difference of 3 seconds. This description of Sysdate () is described in the MySQL Manual as follows: Return to which the function executes.
Sysdate () Date time functions are rarely used in general.
2. Get the current date function: Curdate ()
Mysql> select Curdate ();

+------------+
| Curdate () |
+------------+
| 2008-08-08 |
+------------+

Of these, the following two date functions are equivalent to Curdate ():
Current_date ()
, current_date

3. Get current Time function: Curtime ()
Mysql> select Curtime ();

+-----------+
| Curtime () |
+-----------+
| 22:41:30 |
+-----------+

Where the following two time functions are equivalent to Curtime ():
Current_time ()
, Current_time

4. Obtain the current UTC date-time function: Utc_date (), Utc_time (), Utc_timestamp ()
Mysql> Select Utc_timestamp (), Utc_date (), Utc_time (), now ()

+---------------------+------------+------------+---------------------+
| Utc_timestamp () | Utc_date () | Utc_time () | Now () |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+

Since our country is located in the East Eight time zone, local time = UTC time + 8 hours. UTC time is useful when the business involves multiple countries and regions.

Second, MySQL date time Extract (selection) function.
1. Select various parts of datetime: Date, time, year, quarter, month, day, hour, minute, second, microsecond
Set @dt = ' 2008-09-10 07:15:30.123456 ';

Select Date (@dt); --2008-09-10
Select time (@dt); --07:15:30.123456
Select year (@dt); --2008
Select quarter (@dt); --3
Select Month (@dt); --9
Select Week (@dt); --36
Select Day (@dt); --10
Select Hour (@dt); --7
Select minute (@dt); --15
Select second (@dt); --30
Select Microsecond (@dt); --123456

2. MySQL Extract () function, which can achieve similar functions above:
Set @dt = ' 2008-09-10 07:15:30.123456 ';

Select Extract (year from @dt); --2008
Select Extract (quarter from @dt); --3
Select Extract (month from @dt); --9
Select Extract (week from @dt); --36
Select Extract (Day from @dt); --10
Select Extract (hour from @dt); --7
Select extract (minute from @dt); --15
Select Extract (second from @dt); --30
Select Extract (microsecond from @dt); --123456

Select Extract (Year_month from @dt); --200809
Select Extract (Day_hour from @dt); --1007
Select Extract (Day_minute from @dt); --100715
Select Extract (Day_second from @dt); --10071530
Select Extract (Day_microsecond from @dt); --10071530123456
Select Extract (Hour_minute from @dt); --715
Select Extract (Hour_second from @dt); --71530
Select Extract (Hour_microsecond from @dt); --71530123456
Select Extract (Minute_second from @dt); --1530
Select Extract (Minute_microsecond from @dt); --1530123456
Select Extract (Second_microsecond from @dt); --30123456

The MySQL Extract () function has a full reprehensible function except for the functionality of date () and time (). And also has the choice ' day_microsecond ' and so on function. Note that this is not a selection of day and microsecond, but rather a selection of the days section of the date to the Microsecond section. It's tough enough!
The only bad thing about the MySQL Extract () function is that you need to knock on the keyboard a few more times.
3. MySQL dayof ... Functions: DayOfWeek (), DayOfMonth (), DayOfYear ()
Returns the position of the date parameter, in a week, month, and year, respectively.
Set @dt = ' 2008-08-08 ';

Select DayOfWeek (@dt); --6
Select DayOfMonth (@dt); --8
Select DayOfYear (@dt); --221

The date ' 2008-08-08 ' is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday); 8th day of January; the No. 221 Day of the year.
4. MySQL Week ... Functions: Week (), WeekOfYear (), DayOfWeek (), Weekday (), Yearweek ()
Set @dt = ' 2008-08-08 ';

Select Week (@dt); --31
Select Week (@dt, 3); --32
Select WeekOfYear (@dt); --32

Select DayOfWeek (@dt); --6
Select Weekday (@dt); --4

Select Yearweek (@dt); --200831

MySQL week () function, can have two parameters, specific to see the manual. The WeekOfYear () and week () are calculated as "one day" in the week ordinal of the year. WeekOfYear (@dt) is equivalent to week (@dt, 3).
The MySQL Weekday () function is similar to DayOfWeek () and returns the location of the "Day" in the week. The difference is in reference to the standard, weekday: (0 = Monday, 1 = Tuesday, ..., 6 = Sunday); DayOfWeek: (1 = Sunday, 2 = Monday, ..., 7 = Saturday)
The MySQL Yearweek () function returns year (2008) + Week position (31).
5. MySQL returns the week and month name function: Dayname (), MonthName ()
Set @dt = ' 2008-08-08 ';

Select Dayname (@dt); --Friday
Select MonthName (@dt); --August

Think, how to return the name of Chinese?
6. MySQL Last_day () function: Returns the last day of the month.
Select Last_day (' 2008-02-01 '); --2008-02-29
Select Last_day (' 2008-08-08 '); --2008-08-31

The MySQL Last_day () function is useful, such as I want to get the number of days in the current month, which can be calculated as follows:
Mysql> Select Now (), Day (Last_day (today)) as days;

+---------------------+------+
| Now () | Days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+

Third, MySQL date time calculation function
1. MySQL adds a time interval for the date: Date_add ()
Set @dt = Now ();

Select Date_add (@dt, Interval 1 day); --Add 1 day
Select Date_add (@dt, interval 1 hour); --Add 1 hour
Select Date_add (@dt, interval 1 minute); -- ...
Select Date_add (@dt, Interval 1 second);
Select Date_add (@dt, interval 1 microsecond);
Select Date_add (@dt, Interval 1 week);
Select Date_add (@dt, interval 1 month);
Select Date_add (@dt, interval 1 quarter);
Select Date_add (@dt, interval 1 year);

Select Date_add (@dt, Interval-1 Day); --SUB 1 day

MySQL adddate (), Addtime () function, can be replaced with Date_add (). The following is an example of the Date_add () Implementation addtime () feature:
mysql> Set @dt = ' 2008-08-09 12:12:33 ';

Mysql>
Mysql> Select Date_add (@dt, Interval ' 01:15:30 ' Hour_second);

+------------------------------------------------+
| Date_add (@dt, Interval ' 01:15:30 ' Hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

Mysql> Select Date_add (@dt, Interval ' 1 01:15:30 ' Day_second);

+-------------------------------------------------+
| Date_add (@dt, Interval ' 1 01:15:30 ' Day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

The Date_add () function adds "1 hours 15 minutes 30 seconds" and "1 days 1 hours 15 minutes 30 seconds" to the @dt respectively. Recommendation: Always use the Date_add () Date time function to replace adddate (), Addtime ().
2. MySQL subtracts a time interval from the date: Date_sub ()
Mysql> Select Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second);

+----------------------------------------------------------------+
| Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

The MySQL date_sub () Date time function is consistent with the Date_add () usage and is no longer redundant. In addition, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.
3. MySQL Alternative Date function: Period_add (p,n), Period_diff (P1,P2)
The function argument "P" has the format "yyyymm" or "yymm", and the second argument "n" indicates an increase or subtract of N month (month).
MySQL Period_add (p,n): Date plus/minus N months.
Mysql> Select Period_add (200808,2), Period_add (20080808,-2)

+----------------------+-------------------------+
| Period_add (200808,2) | Period_add (20080808,-2) |
+----------------------+-------------------------+
| 200810 | 20080806 |
+----------------------+-------------------------+

MySQL Period_diff (P1,P2): Date p1-p2, return N months.
Mysql> Select Period_diff (200808, 200801);

+-----------------------------+
| Period_diff (200808, 200801) |
+-----------------------------+
| 9 2
+-----------------------------+

In MySQL, these two date functions are rarely used in general.
4. MySQL date, time subtraction function: DateDiff (DATE1,DATE2), Timediff (time1,time2)
MySQL DateDiff (DATE1,DATE2): Two date subtraction Date1-date2, returns the number of days.
Select DateDiff (' 2008-08-08 ', ' 2008-08-01 '); --7
Select DateDiff (' 2008-08-01 ', ' 2008-08-08 '); ---7

MySQL Timediff (time1,time2): Two date subtraction time1-time2, return time difference value.
Select Timediff (' 2008-08-08 08:08:08 ', ' 2008-08-08 00:00:00 '); --08:08:08
Select Timediff (' 08:08:08 ', ' 00:00:00 '); --08:08:08

Note: The two parameter types of the Timediff (time1,time2) function must be the same.

MySQL Date conversion function, time conversion function
1. MySQL (time, seconds) conversion function: Time_to_sec (times), sec_to_time (seconds)
Select Time_to_sec (' 01:00:05 '); --3605
Select Sec_to_time (3605); -' 01:00:05 '

2. MySQL (date, day) conversion function: To_days (dates), from_days (days)
Select To_days (' 0000-00-00 '); --0
Select To_days (' 2008-08-08 '); --733627

Select From_days (0); --' 0000-00-00 '
Select From_days (733627); --' 2008-08-08 '

3. MySQL Str to date (string conversions to dates) function: str_to_date (str, format)
Select Str_to_date (' 08/09/2008 ', '%m/%d/%y '); --2008-08-09
Select Str_to_date (' 08/09/08 ', '%m/%d/%y '); --2008-08-09
Select Str_to_date (' 08.09.2008 ', '%m.%d.%y '); --2008-08-09
Select Str_to_date (' 08:09:30 ', '%h:%i:%s '); --08:09:30
Select Str_to_date (' 08.09.2008 08:09:30 ', '%m.%d.%y%h:%i:%s '); --2008-08-09 08:09:30

As you can see, str_to_date (Str,format) Conversion functions can convert some messy strings into date formats. In addition, it can also be converted to time. The format is available in the MySQL manual.
4. MySQL Date/time to STR (date/time converted to string) function: Date_format (Date,format), Time_format (Time,format)
Mysql> Select Date_format (' 2008-08-08 22:23:00 ', '%w%m%Y ');

+------------------------------------------------+
| Date_format (' 2008-08-08 22:23:00 ', '%w%m%Y ') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+

Mysql> Select Date_format (' 2008-08-08 22:23:01 ', '%y%m%d%h%i%s ');

+----------------------------------------------------+
| Date_format (' 2008-08-08 22:23:01 ', '%y%m%d%h%i%s ') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

Mysql> Select Time_format (' 22:23:01 ', '%h.%i.%s ');

+-------------------------------------+
| Time_format (' 22:23:01 ', '%h.%i.%s ') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

MySQL date, time conversion function: Date_format (Date,format), Time_format (Time,format) can convert a date/time into a variety of string formats. It is an inverse conversion of the str_to_date (Str,format) function.
5. MySQL obtains the national Region time Format function: Get_format ()
MySQL Get_format () syntax:
Get_format (Date|time|datetime, ' EUR ' | ') USA ' | ' JIS ' | ' ISO ' | ' Internal

All examples of MySQL Get_format () Usage:
Select Get_format (date, ' USA ');--'%m.%d.%y '
Select Get_format (date, ' JIS ');--'% y-%m-%d '
Select Get_format (date, ' ISO ');--'%y-%m-%d '
Select Get_format (date, ' EUR ');--'%d.%m.%y '
Select GE T_format (date, ' internal '); --'%y%m%d '
Select Get_format (datetime, ' USA ');--'%y-%m-%d%h.%i.%s '
Select Get_format (datetime, ' JIS ');--'%y- %m-%d%h:%i:%s '
Select Get_format (datetime, ' ISO ');--'%y-%m-%d%h:%i:%s '
Select Get_format (DateTime, ' EUR '); '%y-%m-%d%h.%i.%s '
Select Get_format (datetime, ' internal ');--'%y%m%d%h%i%s '
Select Get_format (Time, ' USA ');- -'%h:%i:%s%p '
Select Get_format (Time, ' JIS ');--'%h:%i:%s '
Select Get_format (time, ' ISO ');--'%h:%i:%s '
SE Lect Get_format (Time, ' EUR '); --'%h.%i.%s '
Select Get_format (Time, ' internal ');--'%h%i%s '

The MySQL Get_format () function uses fewer opportunities in practice.
6. MySQL Patchwork Date, time function: Makdedate (year,dayofyear), Maketime (Hour,minute,second)
Select Makedate (2001,31); --' 2001-01-31 '
Select Makedate (2001,32); --' 2001-02-01 '

Select Maketime (12,15,30); -' 12:15:30 '

Five, MySQL timestamp (Timestamp) function
1. MySQL Gets the current timestamp function: Current_timestamp, Current_timestamp ()
Mysql> Select Current_timestamp, Current_timestamp ();

+---------------------+---------------------+
| Current_timestamp | Current_timestamp () |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

2. MySQL (Unix timestamp, date) conversion function:
Unix_timestamp (),
Unix_timestamp (date),
From_unixtime (Unix_timestamp),
From_unixtime (Unix_timestamp,format)

Here is an example:
Select Unix_timestamp (); --1218290027
Select Unix_timestamp (' 2008-08-08 '); --1218124800
Select Unix_timestamp (' 2008-08-08 12:30:00 '); --1218169800

Select From_unixtime (1218290027); -' 2008-08-09 21:53:47 '
Select From_unixtime (1218124800); -' 2008-08-08 00:00:00 '
Select From_unixtime (1218169800); -' 2008-08-08 12:30:00 '

Select From_unixtime (1218169800, '%Y%d%m%h:%i:%s%x '); --' 2008 8th August 12:30:00 2008 '

3. MySQL timestamp (timestamp) conversion, increase, subtraction function:
Timestamp (date)--date to timestamp
Timestamp (dt,time)--DT + Time
Timestampadd (unit,interval,datetime_expr)--
Timestampdiff (UNIT,DATETIME_EXPR1,DATETIME_EXPR2)--

Take a look at the Example section:
Select timestamp (' 2008-08-08 '); --2008-08-08 00:00:00
Select Timestamp (' 2008-08-08 08:00:00 ', ' 01:01:01 '); --2008-08-08 09:01:01
Select Timestamp (' 2008-08-08 08:00:00 ', ' 10 01:01:01 '); --2008-08-18 09:01:01

Select Timestampadd (Day, 1, ' 2008-08-08 08:00:00 '); --2008-08-09 08:00:00
Select Date_add (' 2008-08-08 08:00:00 ', Interval 1 day); --2008-08-09 08:00:00

The MySQL Timestampadd () function is similar to Date_add ().
Select Timestampdiff (year, ' 2002-05-01 ', ' 2001-01-01 '); ---1
Select Timestampdiff (Day, ' 2002-05-01 ', ' 2001-01-01 '); ---485
Select Timestampdiff (Hour, ' 2008-08-08 12:00:00 ', ' 2008-08-08 00:00:00 '); ---12

Select DateDiff (' 2008-08-08 12:00:00 ', ' 2008-08-01 00:00:00 '); --7

MySQL time Zone (timezone) conversion function
Convert_tz (Dt,from_tz,to_tz)

Select Convert_tz (' 2008-08-08 12:00:00 ', ' +08:00 ', ' +00:00 '); --2008-08-08 04:00:00

Time zone conversions can also be achieved by Date_add, Date_sub, Timestampadd.
Select Date_add (' 2008-08-08 12:00:00 ', Interval-8 hour); --2008-08-08 04:00:00
Select Date_sub (' 2008-08-08 12:00:00 ', Interval 8 hour); --2008-08-08 04:00:00

Select To_days (str_to_date (' 12/1/2001 12:00:00 AM ', '%m/%d/%y '))-
To_days (Str_to_date (' 11/28/2001 12:00:00 AM ', '%m/%d/%y ')) as a from table1