MySQL time function
A. timestampdiff () passes three parameters, the first time type such as year, month, day, the second start time, and the third end time
Select test_name, timestampdiff (YEAR,create_time,end_time) y_date from test_table;-Computing time
+-+ +
| | test_name | y_date |
+-+ +
| | Harold | 29 |
| | Harold | 17 | |
| | Gwen | 13 |
| | Benny | 110 | |
+-+ +
B. INTERVAL n day day can also be replaced with year, month, etc.
Select now () + INTERVAL 1 day;-for tomorrow
+-- +
| | now () + INTERVAL 1 day |
+-- +
| | 2018-12-07 09:52:03 |
+-- +
C. last_day () add a time date in parentheses to indicate the end of the month
Select last_day (now ());-- indicates the end of the month
+-+
| | last_day (now ()) |
+-+
| | 2018-12-31 | |
+-+
D. extract (day from now ()) indicates the day of a month, day can also be replaced with year,month, etc.
Select date_sub (date (now ()), interval extract (day from now ())-1 day) as' month_firstday'; at the beginning of the month
+-+
| | month_firstday |
+-+
| | 2018-12-01 | |
+-+
E. date_format () converts a date / time into various string formats
Select date_format (now (),'% Y-%m-%d% HRV% iRV% s');% Y: year,% m: month,% M: English month,% d: day,% D: English day,% h: hour,% HRV 24-hour system,% I: minutes,% s: seconds
+-+
| | date_format (now (),'% Y-%m-%d% HRV% iRV% s') |
+-+
| | 2018-12-06 10:28:04 |
+-+
F. str_to_date () converts a string format date / time to time
Select str_to_date ('07.10.2017 08 as st_to_d 09 as st_to_d 308,'% m.%d.%Y% HV% iRO% s')
+-+
| | st_to_d |
+-+
| | 2017-07-10 08:09:30 |
+-+
1 row in set (0.06 sec)
G. date_add (), date_sub ()
Select date_add (now (), interval 1 day) as add_day; plus one day means tomorrow
+-+
| | add_day |
+-+
| | 2018-12-07 10:46:50 |
+-+
1 row in set (0.00 sec)
Select date_add (now (), interval 1 day) as add_day; minus one day, indicating yesterday
+-+
| | sub_day |
+-+
| | 2018-12-05 10:48:48 |
+-+
1 row in set (0.00 sec)
H. select week (now ()); week ordinal of the year
Select quarter (now ()); quarterly
Select dayofweek (now ()); day of the week
Select dayofmonth (now ()); the day of the month
Select dayofyear (now ()); the day of the year
Show variables like 'lc_time_names'
Select dayname (now ()), monthname (now ()); English day of the week, English month
+-+ +
| | dayname (now ()) | monthname (now ()) | |
+-+ +
| | Thursday | December |
+-+ +
Set lc_time_names='zh_CN'; time name changed to Chinese
+-+ +
| | dayname (now ()) | monthname (now ()) | |
+-+ +
| | Thursday | December | |
+-+ +
