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

This article mainly introduces the date format conversion functions in mysql, which are very detailed and have certain reference value. Friends who are interested must finish reading them.

Mysql date format conversion functions are: 1, [SEC_TO_TIME (seconds)] converts seconds into time; 2, [TIME_TO_SEC (time)] converts time into seconds; 3, [ADDTIME (time,times)] adds times to time.

The mysql date format conversion functions are:

1. Get the current date of the system

SELECT CURDATE () SELECT CURRENT_DATE ()-> 2016-01-16-> 2016-01-16

2. Get the current system time

SELECT CURTIME () SELECT CURRENT_TIME ()-> 17move44move22-> 17:44:22

3. NOW (), SYSDATE (), CURRENT_TIMESTAMP (), LOCALTIME (): get the current date and time of the system

SELECT NOW () SELECT SYSDATE () SELECT CURRENT_TIMESTAMP () SELECT CURRENT_TIMESTAMP SELECT LOCALTIME () SELECT LOCALTIME- > 2016-01-16 17 purl 441-> 2016-01-16 17 purl 441-> 2016-01-16 17 VLV 44 41-> 2016-01-16 17 VLV 44 41-> 2016-01-16 17RV 44 purl 41-> 2016-01-16 17:44:41

4. UNIX_TIMESTAMP (date): get the timestamp

SELECT UNIX_TIMESTAMP () SELECT UNIX_TIMESTAMP ('2016-01-16') SELECT UNIX_TIMESTAMP ('2016-01-16 23 SELECT UNIX_TIMESTAMP 59')-> 1452937627-> 1452873600-> 1452959999

5. FROM_UNIXTIME (unix_timestamp,format): convert the timestamp to date and time

SELECT FROM_UNIXTIME (1452959999) SELECT FROM_UNIXTIME (1452959999) 99% Ymuri% mmurf% d% HRV% iRV% s')-> 2016-01-16 23 59-> 2016-01-16 23:59:59

6. SEC_TO_TIME (seconds): convert seconds into time

SELECT SEC_TO_TIME (2378)-> 00:39:38

7. TIME_TO_SEC (time): convert time into seconds

SELECT TIME_TO_SEC ('22 2300')-> 2378

8. ADDTIME (time,times): add times to time

SELECT ADDTIME ("2015-12-31 23:59:59",'01-01-01-01 01:01:00 ")

9. CONVERT_TZ (date,from_tz, to_tz): convert the time zone

SELECT CONVERT_TZ ('2004-01-01 12-0-0-01-12)-> 2004-01-01 22:00:00

10. STR_TO_DATE (date,format): date time that converts a string into format format

SELECT STR_TO_DATE ('2015-01-01-01,'% YMY% mmury% d')-> 2015-01-01

11. LAST_DAY (date): gets the date of the last day of the month of the date

SELECT LAST_DAY (SYSDATE ()) SELECT LAST_DAY ('2015-02-02') SELECT LAST_DAY ('2015-02-02-02 SELECT LAST_DAY 22-33')-> 2016-01-31-> 2015-02-28-> 2015-02-28

12. MAKEDATE (year, dayofyear): get the date according to the parameter (year, number of days)

SELECT MAKEDATE (2015, 32)-> 2015-02-01

13. MAKETIME (hour, minute, second): get the time according to the parameters (hours, minutes, seconds)

SELECT MAKETIME (12, 23, 34)-> 12:23:34

14. YEARWEEK (date): get the year and week of the date

SELECT YEARWEEK (SYSDATE ()) SELECT YEARWEEK ('2015-01-10') SELECT YEARWEEK ('2015-01-10)-> 201602-> 201501-> 201502

15. WEEKOFYEAR (date): in what week of the year is the day of acquisition?

SELECT WEEKOFYEAR (SYSDATE ()) SELECT WEEKOFYEAR ('2015-01-10')-> 2-> 2

Several time format conversion functions commonly used in mysql are organized as follows

1Fromstarting unixtime (timestamp, format):

Timestamp is int time. For example, 14290450779 format is the converted format, which includes the following format:

% M month name (January... December)

% W week name (Sunday... Saturday)

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% Y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (Sun... Sat)

Number of days in% d month, number (00. 31)

Number of days in% e month, number (0... 31)

% m month, number (01... 12)

% c month, number (1 …... 12)

% b abbreviated month name (Jan... Dec)

% j days of the year (001... 366)

% H hours (00. 23)

% k hours (0. 23)

% h hours (01 …... 12)

% I hours (01 …... 12)

% l hours (1 …... 12)

% I minutes, number (00. 59)

% r time, 12 hours (hh:mm:ss [AP] M)

% T time, 24 hours (hh:mm:ss)

% s seconds (00. 59)

% s seconds (00. 59)

% p AM or PM

% w days in a week (0=Sunday... 6=Saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. 52), here Monday is the first day of the week

2 unix recording timestamp (date):

The effect is just the opposite of from_unixtime (), which converts unix timestamps into readable times, while unix_timestamp () converts readable times into unix timestamps, which are used when sorting datetime stored times. For example, unix_timestamp ('2009-08-06 10 10 40'), you get 1249524739.

If unix_timestamp () does not pass an argument, the now () function is called to fetch the current time automatically.

3 date datekeeper format (date, format):

Date_format () converts date or datetime type values to any time format. For example, in a common application scenario, a table has a field whose update time is updated and stores the datetime type, but the foreground only needs to display the xxxx-xx-xx. At this time, it can be processed with date_format (date,'%Y-%m-%d') instead of using a program loop in the result set.

The above is all the contents of the date format conversion function in mysql. Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.