Shulou(Shulou.com)06/01 Report--

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 | |

+-+ +

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.