Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the date functions in MySQL. I hope you will get something after reading this article. Let's discuss it together.

Date function type:

(1) the function to get the current date and the function to get the current time

(2) the function to get the current date and time

(3) UNIX timestamp function

(4) function that returns UTC date and UTC time

(5) get the functions month (date) and monthname (date) of the month

(6) get the week's functions dayname (d), dayofweek (d) and weekday (d)

(7) get the functions week (d) and dayofyear (d) of the week

(8) the functions dayofyear (d) and dayofmonth (d) that get the number of days

(9) get the functions of year, quarter, hour, minute and second.

(10) the function extract (type from date) that gets the specified value of the date

(11) function of time and second conversion

(12) function for calculating date and time

(13) functions that format dates and times

(recommended for free study: mysql video tutorial)

(1) the function to get the current date and the function to get the current time

1.curdate () and current_date ()

[example] use the date function to get the current date of the system. The SQL statement is as follows:

Mysql > select curdate (), current_date (), curdate () + 0 +-+ | curdate () | current_date () | curdate () + 0 | +-+ | 2019-08-18 | 2019-08-18 | 20190818 | +-+ 1 row in set (0.00 sec)

Curdate () + 0 converts the current date value to a numeric value.

2.curtime () and current_time ()

[example] use the time function to obtain the current time of the system. The SQL statement is as follows:

Mysql > select curtime (), current_time (), curtime () + 0 +-+ | curtime () | current_time () | curtime () + 0 | +-+ | 17:08:07 | 17:08:07 | 170807 | | +-+ 1 row in set (0.00 sec) |

Curtime () + 0 converts the current time value to a numeric value.

(2) the function to get the current date and time

The four functions current_timestamp (), localtime (), now (), and sysdate () do the same thing, returning the current date and time values.

[example] use the date-time function to get the current system time and date. The SQL statement is as follows:

Mysql > select current_timestamp ()-> localtime (),-> now (),-> sysdate () +-+ | current_timestamp () | localtime () | now () | sysdate () | +-+ | 2019-08-18 19:10:05 | 2019-08-18 19:10:05 | 2019-08-18 19: 10:05 | 0-08-18 19:10:05 | +-+ 1 row in set (2019 sec) (3) UNIX timestamp function

If unix_timestamp (date) is called without parameters, a unix timestamp (seconds after GMT, GMT is 1970.1.1 GMT) is returned as an unsigned integer.

Date can be a date string, datetime string, timestamp, or a local time YY [YY] MMDD format number.

1.unix_timestamp (date)

[example] use the unix_timestamp function to return a timestamp in unix format. The SQL statement is as follows:

Mysql > select unix_timestamp (), unix_timestamp (now ()), now () +-+-- +-+ | unix_timestamp () | unix_timestamp (now ()) | now () | +-+- -+ | 1566127316 | 1566127316 | 2019-08-18 19:21:56 | + -+ 1 row in set (0.05 sec)

The from_unixtime () function converts the unix timestamp into a normal format of time, which is an inverse function to the unix_timestamp (date) function.

2.from_unixtime (date)

[example] use the from_unixtime function to convert the unix timestamp into normal format time. The SQL statement is as follows:

Mysql > select from_unixtime ('1566127316') +-- + | from_unixtime ('1566127316') | +-- + | 2019-08-18 19 21V 56.000000 | +-+ 1 row in set (0.00) Sec) (4) the function that returns the UTC date and the UTC time.

1.UTC_DATE ()

[example] use the utc_date () function to return the current UTC date value. The SQL statement is as follows:

Mysql > select utc_date (), utc_date () + 0 utc_date () | utc_date () + 0 | +-+-+ | 2019-08-18 | 20190818 | +-+-+ 1 row in set (0.05 sec)

2.UTC_TIME ()

[example] use the UTC_TIME () function to return the current UTC time value. The SQL statement is as follows:

Mysql > select utc_time (), utc_time () + 0 +-+-+ | utc_time () | utc_time () + 0 | +-+-+ | 11:32:27 | 113227 | +-+-+ 1 row in set (0.00 sec) ( 5) get the functions month (date) and monthname (date) of the month

1.month (date)

[example] use the month () function to return the month in the specified date. The SQL statement is as follows:

Mysql > select month ('2019-08-18'); +-+ | month (' 2019-08-18') | +-+ | 8 | +-+ 1 row in set (0.00 sec)

2.monthname (date)

[example] use the monthname () function to return the month name in the specified date. The SQL statement is as follows:

Mysql > select monthname ('2019-08-18') +-+ | monthname ('2019-08-18') | +-+ | August | +-+ 1 row in set (0.00 sec) (6) get the week's function dayname (d), Dayofweek (d) and weekday (d)

1.dayname (d)

[example] use the dayname () function to return the working day name of the specified date. The SQL statement is as follows:

Mysql > select dayname ('2019-08-18'); +-+ | dayname (' 2019-08-18') | +-+ | Sunday | +-+ 1 row in set (2019 sec)

2.dayofweek (d)

[example] use the dayofweek () function to return the weekly index corresponding to the date. The SQL statement is as follows:

Mysql > select dayofweek ('2019-08-18'); +-+ | dayofweek (' 2019-08-18') | +-+ | 1 | +-+ 1 row in set (0.00 sec)

3.weekday (d)

Weekday (d) returns the working day index corresponding to d: 0 for Monday, 1 generation for Tuesday, … 6 stands for Sunday.

[example] use the weekday () function to return the working day index corresponding to the date. The SQL statement is as follows:

Mysql > select weekday ('2019-08-18 19 weekday 40-0'),-> 18-08-18) +-+ | weekday ('2019-08-18 1915 1915 40 00') | weekday (' 2019-08-18') | +-+-- -+ | 6 | 6 | +-- +-- + 1 row in set (0.00 sec) (7) Functions week (d) and dayofyear (d) that take the day of the week

Week (d) the calculation date d is the week of the year, and the two-parameter form allows you to specify whether the week begins on Sunday or Monday, using the value of the default_week_format system argument if the Mode parameter is ignored.

1.week (d)

[example] use the week () function to query the week in which the specified date is. The SQL statement is as follows:

Mysql > select week ('2019-08-18'), week ('2019-08-18), week (' 2019-08-18) +-+-+ | week ('2019-08-18') | week ('2019-08-18) | week (' 2019-08-18') 1) | +-- +-- + | 33 | 33 | 33 | +- -- + 1 row in set (0.05 sec

2.weekofyear (d)

[example] use weekofyear () query to specify the week of the year, and the SQL statement is as follows:

Mysql > select week ('2019-08-18), weekofyear (' 2019-08-18') +-+ | week ('2019-08-18' 3) | weekofyear ('2019-08-18') | +-+-+ | 33 | 33 | +-+- -+ 1 row in set (0.05sec) (8) the functions dayofyear (d) and dayofmonth (d) to get the number of days

1.dayofyear ()

[example] use the dayofyear () function to return the location of the specified date in a year. The SQL statement is as follows:

Mysql > select dayofyear ('2019-08-18'); +-+ | dayofyear ('2019-08-18') | +-+ | 230 | +-+ 1 row in set (0.00 sec)

2.dayofmonth ()

[example] use the dayofmonth () function to return the position of the specified date in a month. The SQL statement is as follows

Mysql > select dayofmonth ('2019-08-18') +-- + | dayofmonth ('2019-08-18') | +-+ | 18 | +-+ 1 row in set (0.00 sec) (9) get year, quarter, hour, A function of minutes and seconds.

1.YEAR (date)

[example] use the year () function to return the year corresponding to the specified date. The SQL statement is as follows:

Mysql > select year ('19-08-18'), year ('98-02-19') +-+ | year ('19-08-18') | year ('98-02-19') | +-+-+ | 2019 | 1998 | +- -+-+ 1 row in set (0.05sec)

2.QUARTER (date)

[example] use the quarter () function to return the quarter corresponding to the specified date. The SQL statement is as follows:

Mysql > select quarter ('19-08-18'); +-+ | quarter ('19-08-18') | +-+ | 3 | +-+ 1 row in set (0.00 sec)

3.MINUTE (time)

[example] use the minute () function to return the minute value of the specified time. The SQL statement is as follows:

Mysql > select minute ('19-08-18 20 purl 07lv 00') +-- + | minute ('19-08-18 20 sec 07sec') | +-+ | 7 | +-+ 1 sec)

4.SECOND (time)

[example] use the second () function to return the second value of the specified time. The SQL statement is as follows:

Mysql > select second ('20 row in set 07 sec 00'); +-+ | extract (type from date) to get the specified value of the date

Use the extract (type from date) function to extract the date or time value.

Mysql > select extract (year from '2019-08-18') as col1,-> extract (year_month from' 2019-08-18 20 year from 46 46 as col1 01') as col2,-> extract (day_minute from '2019-08-18 20 20 year from 46) as col3 +-+ | col1 | col2 | col3 | +-+ | 2019 | 201908 | 182046 | +-1 row in set (0.00 sec) (11) time and second conversion function

1.time_to_sec (time)

Time_to_sec (time) returns the time parameter that has been converted to seconds. The conversion formula is: hours x 3600 + minutes * 60 + seconds.

[example] use the time_to_sec function to convert the time value to the second value.

Mysql > select time_to_sec ('20 row in set 34 sec'); +-+ | time_to_sec ('20 row in set 34 sec') | +-+ | 0 |

2.sec_to_time (seconds)

The return value of the sec_to_time function becomes a decimal value after adding a value of 0.

Time_to_sec and sec_to_time happen to be inverse functions.

[example] use the sec_to_time () function to convert the second value into a time format, and the SQL statement is as follows

Mysql > select sec_to_time (2345), sec_to_time (2345) + 0,-> time_to_sec ('20 sec_to_time 36 time_to_sec'), sec_to_time ('74040') +-- + | sec_to_time (2345) | sec_to_time (2345) + 0 | time_to_sec ('20: 36Rank 00') | sec_to_time ('74040') | +-- + | 00:39:05 | 3905 | 74160 | 20 row 34 row 00.000000 | +-- + 1 In set (0. 05 sec) (12) A function that calculates the date and time.

The format for calculating the date and time in MySQL:

The functions of 1.date_add (date,interval expr type) and adddate (date,interval expr type) are the same, performing the addition of dates:

[example] use the date_add () and adddate () functions to perform the date addition operation. The SQL statement is as follows:

Mysql > select date_add ('2019-08-18 23 as col1 59 second second) as col1,-> adddate (' 2019-08-18 23 second 59 second) as col2,-> date_add ('2019-08-18 23 22 14 59 as col1) as col3 +-+ | col1 | col2 | col3 | + -+ | 2019-08-19 00:00:00 | 2019-08-19 00:00:00 | 2019-08-19 00:01:00 | +- -+ 1 row in set (0.05 sec)

The two functions 2.date_sub (date,interval expr type) and subdate (date,interval expr type) have the same effect, and the subtractive operation of the execution date:

[example] use date_sub and subdate functions to perform date subtraction. The SQL statement is as follows:

Mysql > select date_sub (interval 31 day) as col1,-> subdate (interval 31 day) as col2,-> date_sub ('2019-08-18 21 as col1) as col3) +-+ | col1 | col2 | col3 | +-+ | 2019-07- 18 | 0-07-18 | 2019-08-18 21:14:09 | +-+ 1 row in set (2019 sec)

The 3.addtime (date,expr) function adds the expr value to date and returns the modified value, where date is a date or date-time expression and expr is a time expression.

[example] use addtime for time addition operation. The SQL statement is as follows

Mysql > select addtime ('2019-08-18 21 addtime 59)), addtime (' 02V 02V 02V 02L 02L 02L 00L 00') +-- +-- + | addtime ('2019-08-18 21 59 59) 1V 1V 1') | addtime ('02VR 02VR 02') '02glaze 0000') | +-+-+ | 2019-08-18 23:01:00 | 04:02:02 | + -- +-- + 1 row in set (0.05 sec)

The 4.subtime (date,expr) function subtracts the date from the exp value and returns the modified value, date is a date or date-time expression, and expr is a time expression.

[example] use the subtime () function to perform the subtraction operation. The SQL statement is as follows:

Mysql > select subtime ('2019-08-18 21 subtime 59)), subtime (' 02V 02V 02V 02L 02L 02L 00L 00') +-- +-- + | subtime ('2019-08-18 21 59 59) 1V 1V 1') | subtime ('02VR 02VR 02') '02glaze 0000') | +-+-+ | 2019-08-18 20:58:58 | 00:02:02 | + -+-- + 1 row in set (0.00 sec)

5.datediff (date1,date2) returns the number of days between the start time date1 and the end time date2, where date1 and date2 are dates or date-and-time expressions. Only the date portion of these values is used in the calculation.

[example] use the datediff () function to calculate the number of days between two dates. The SQL statement is as follows

Mysql > select datediff ('2019-08-18 21 as col1 59) as col1,-> datediff (' 2019-08-18 22 7-22) as col2 +-+-+ | col1 | col2 | +-+-+ | 396 |-2 | +-+-+ 1 row in set (0.00 sec) (13) function that formats the date and time.

DATE_FORMAT time date format:

1.date_format ()

[example] use the date_format () function to format the output date and time values. The SQL statement is as follows:

Mysql > select date_format ('2019-08-18 23) as col1% m% y') as col2,-> date_format (' 2019-08-18 23 as col1 33%)% d% a% m% b% j') +-+-+ | col1 | col2 | +-+-+ | 0.0819 | 18th 19 Sun 18 08 Aug 230 | +-+- -+ 1 row in set (0.05 sec)

2.time_format ()

[example] use the time_format (time,format) function to format the input time value. The SQL statement is as follows:

Mysql > select time_format ('23RV 39Rd 30th)% H% k% h% I% l') +-- + | time_format ('23RV 39RV 30' '% H% k% h% I% l') | +-+ | 23 23 11 11 11 | +-+ 1 row in set (0.00 sec)

3.get_format ()

The format string returned by get_format:

[example] use the get_format () function to display format strings under different formatting types. The SQL statement is as follows:

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.05 sec)

[example] in the date_format () function, the specified date value is displayed using the display format string returned by the get_format function. The SQL statement is as follows:

Mysql > select date_format ('2019-08-19 23-41-12-31-41-31-31-31-31-19-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-14-12-12-14-12-14-12-14-14-12-14-14-14-12-14-12-14-14-14-12-14, respectively.) mysql > select date_format (' 2019-08-19 23-41-19-21) +-+ | date_format ('2019-08-19 2314 41 purl 30 recording and recording format (date) 'usa')) | +-- + | 08.19.2019 | +- -- + 1 row in set (0.00 sec) finished reading this article I believe you have a certain understanding of "what are the date functions in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.