MySQL introduces date functions
- coldplay.xixiforward
- 2021-03-02 09:11:292476browse
Date function type:
(1) Function to get the current date and function to get the current time
(2) Function to obtain the current date and time
(3) UNIX timestamp function
(4) Function to return UTC date and function to return UTC time
(5) Function to obtain month month( date) and monthname(date)
(6) The functions dayname(d), dayofweek(d) and weekday(d)
(7) The functions week(d) and dayofyear(d) to obtain the week number )
(8) Functions to get the number of days dayofyear(d) and dayofmonth(d)
(9) Functions to get the year, quarter, hour, minute and second.
(10) Function to get the specified value of date extract(type from date)
(11) Function to convert time and seconds
(12) Function to calculate date and time
(13) Function to format date and time
(Related free learning recommendations: mysql video tutorial)
(1) Function to get the current date and function to get the current time
1.curdate() and current_date()
[Example] Use date function to obtain 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 means converting the current date value to a numeric type.
2.curtime() and current_time()
[Example] Use time function to obtain the current system time, 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 means converting the current time value into a numeric type.
(2) Function to obtain the current date and time
- The four functions of current_timestamp(), localtime(), now() and sysdate() The same, both return the current date and time value.
[Example] Use the date and time function to obtain 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 | 2019-08-18 19:10:05 |+---------------------+---------------------+---------------------+---------------------+1 row in set (0.05 sec)
(3)UNIX timestamp function
- unix_timestamp(date), if called without parameters, returns a unix timestamp (the number of seconds after GMT, GMT is Greenwich Mean Time 1970.1.1) as an unsigned integer.
- Date can be a date string, datetime string, timestamp or a number in YY[YY]MMDD format of local time.
1.unix_timestamp(date)
[Example] Use the unix_timestamp function to return the 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)
- from_unixtime() function The unix timestamp is converted into a time in a common format, and is the inverse function of the unix_timestamp(date) function.
2.from_unixtime(date)
[Example] Use the from_unixtime function to convert the unix timestamp into a common format time. The SQL statement is as follows:
mysql> select from_unixtime('1566127316');+-----------------------------+| from_unixtime('1566127316') |+-----------------------------+| 2019-08-18 19:21:56.000000 |+-----------------------------+1 row in set (0.00 sec)
(4 ) function that returns UTC date and function that returns 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 month functions month(date) and monthname(date)
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 Specify the month name in the 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 functions of 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 (0.00 sec)
2.dayofweek(d)
[Example] Use dayofweek() The function returns the week 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 weekday index corresponding to d: 0 represents Monday. Generation 1 represents Tuesday,...6 represents 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:40:00'),
-> weekday('2019-08-18');+--------------------------------+-----------------------+| weekday('2019-08-18 19:40:00') | weekday('2019-08-18') |+--------------------------------+-----------------------+| 6 | 6 |+--------------------------------+-----------------------+1 row in set (0.00 sec)
(7) The function week(d) to obtain the week number ) and dayofyear(d)
- week(d) calculate the day d is the week of the year. The double-parameter form allows you to specify whether the week starts on Sunday or Monday. If the Mode parameter is Ignore and use the value of the default_week_format system argument.
1.week(d)
[Example] Use the week() function to query the week of the year for a specified date. The SQL statement is as follows:
mysql> select week('2019-08-18'),week('2019-08-18',0),week('2019-08-18',1);+--------------------+----------------------+----------------------+| week('2019-08-18') | week('2019-08-18',0) | week('2019-08-18',1) |+--------------------+----------------------+----------------------+| 33 | 33 | 33 |+--------------------+----------------------+----------------------+1 row in set (0.05 sec
2 .weekofyear(d)
[Example] Use weekofyear() to query the week of the year for a specified date. The SQL statement is as follows:
mysql> select week('2019-08-18',3),weekofyear('2019-08-18');+----------------------+--------------------------+| week('2019-08-18',3) | weekofyear('2019-08-18') |+----------------------+--------------------------+| 33 | 33 |+----------------------+--------------------------+1 row in set (0.05 sec)
(8) Function dayofyear() to obtain the number of days d) and dayofmonth(d)
1.dayofyear()
[Example] Use the dayofyear() function to return the position of the specified date in the 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 the year, quarter, hour, Functions 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.05 sec)
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:07:00');+-----------------------------+| minute('19-08-18 20:07:00') |+-----------------------------+| 7 |+-----------------------------+1 row in set (0.00 sec)
4.SECOND(time)
[Example] Use the second() function to return the seconds value of the specified time. The SQL statement is as follows:
mysql> select second('20:07:00');+--------------------+| second('20:07:00') |+--------------------+| 0 |+--------------------+1 row in set (0.00 sec)
(10)获取日期的指定值的函数extract(type from date)
【例】使用extract(type from date)函数提取日期或时间值。
mysql> select extract(year from '2019-08-18') as col1, -> extract(year_month from '2019-08-18 20:46:01') as col2, -> extract(day_minute from '2019-08-18 20:46:01') as col3;+------+--------+--------+| col1 | col2 | col3 |+------+--------+--------+| 2019 | 201908 | 182046 |+------+--------+--------+1 row in set (0.00 sec)
(11)时间和秒钟转换的函数
1.time_to_sec(time)
- time_to_sec(time)返回已经转化为秒的time参数。转换公式为：小时x3600+分钟*60+秒。
【例】使用time_to_sec函数将时间值转换为秒值。
mysql> select time_to_sec('20:34:00');+-------------------------+| time_to_sec('20:34:00') |+-------------------------+| 74040 |+-------------------------+1 row in set (0.00 sec)
2.sec_to_time(seconds)
- sec_to_time函数返回值加上0值之后变成了小数值。
- time_to_sec正好和sec_to_time互为反函数。
【例】使用sec_to_time()函数将秒值转换为时间格式,SQL语句如下;
mysql> select sec_to_time(2345),sec_to_time(2345)+0,
-> time_to_sec('20:36:00'),sec_to_time('74040');+-------------------+---------------------+-------------------------+----------------------+| sec_to_time(2345) | sec_to_time(2345)+0 | time_to_sec('20:36:00') | sec_to_time('74040') |+-------------------+---------------------+-------------------------+----------------------+| 00:39:05 | 3905 | 74160 | 20:34:00.000000 |+-------------------+---------------------+-------------------------+----------------------+1 row in set (0.05 sec)
(12)计算日期和时间的函数。
MySQL中计算日期和时间的格式:
1.date_add(date,interval expr type)和adddate(date,interval expr type)两个函数的作用相同,执行日期的加运算:
【例】使用date_add()和adddate()函数执行日期加操作,SQL语句如下:
mysql> select date_add('2019-08-18 23:59:59',interval 1 second) as col1,
-> adddate('2019-08-18 23:59:59',interval 1 second) as col2,
-> date_add('2019-08-18 23:59:59',interval '1:1' minute_second) 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)
2.date_sub(date,interval expr type)和subdate(date,interval expr type)两个函数作用相同，执行日期的减运算：
【例】使用date_sub和subdate函数执行日期减操作，SQL语句如下：
mysql> select date_sub('2019-08-18',interval 31 day) as col1,
-> subdate('2019-08-18',interval 31 day) as col2,
-> date_sub('2019-08-18 21:15:10',interval '0 0:1:1' day_second) as col3;+------------+------------+---------------------+| col1 | col2 | col3 |+------------+------------+---------------------+| 2019-07-18 | 2019-07-18 | 2019-08-18 21:14:09 |+------------+------------+---------------------+1 row in set (0.00 sec)
3.addtime(date,expr)函数将expr值添加到date,并返回修改后的值,date是一个日期或者日期时间表达式,而expr是一个时间表达式。
【例】使用addtime进行时间加操作，SQL语句如下;
mysql> select addtime('2019-08-18 21:59:59','1:1:1'),addtime('02:02:02','02:00:00');+----------------------------------------+--------------------------------+| addtime('2019-08-18 21:59:59','1:1:1') | addtime('02:02:02','02:00:00') |+----------------------------------------+--------------------------------+| 2019-08-18 23:01:00 | 04:02:02 |+----------------------------------------+--------------------------------+1 row in set (0.05 sec)
4.subtime(date,expr)函数将date减去expr值，并返回修改后的值，date是一个日期或者日期时间表达式，expr是一个时间表达式。
【例】使用subtime()函数执行减操作，SQL语句如下：
mysql> select subtime('2019-08-18 21:59:59','1:1:1'),subtime('02:02:02','02:00:00');+----------------------------------------+--------------------------------+| subtime('2019-08-18 21:59:59','1:1:1') | subtime('02:02:02','02:00:00') |+----------------------------------------+--------------------------------+| 2019-08-18 20:58:58 | 00:02:02 |+----------------------------------------+--------------------------------+1 row in set (0.00 sec)
5.datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数，date1和date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。
【例】使用datediff()函数计算两个日期之间的间隔天数,SQL语句如下;
mysql> select datediff('2019-08-18 21:59:59','2018-07-18') as col1,
-> datediff('2019-08-18 22:00:00','2019-08-20') as col2;+------+------+| col1 | col2 |+------+------+| 396 | -2 |+------+------+1 row in set (0.00 sec)
(13)将日期和时间格式化的函数。
DATE_FORMAT时间日期格式:
1.date_format()
【例】使用date_format()函数格式化输出日期和时间值,SQL语句如下:
mysql> select date_format('2019-08-18 23:33:00','%w %m %y') as col1,
-> date_format('2019-08-18 23:33:00','%D %y %a %d %m %b %j') as col2;+---------+---------------------------+| col1 | col2 |+---------+---------------------------+| 0 08 19 | 18th 19 Sun 18 08 Aug 230 |+---------+---------------------------+1 row in set (0.05 sec)
2.time_format()
【例】使用time_format(time,format)函数格式化输入时间值,SQL语句如下:
mysql> select time_format('23:39:30','%H %k %h %I %l');+------------------------------------------+| time_format('23:39:30','%H %k %h %I %l') |+------------------------------------------+| 23 23 11 11 11 |+------------------------------------------+1 row in set (0.00 sec)
3.get_format()
get_format返回的格式字符串:
【例】使用get_format()函数显示不同格式化类型下的格式字符串,SQL语句如下:
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)
【例】在date_format()函数中,使用get_format函数返回的显示格式字符串来显示指定的日期值,SQL语句如下:
mysql> select date_format('2019-08-19 23:41:30',get_format(date,'usa'));+-----------------------------------------------------------+| date_format('2019-08-19 23:41:30',get_format(date,'usa')) |+-----------------------------------------------------------+| 08.19.2019 |+-----------------------------------------------------------+1 row in set (0.00 sec)
相关免费学习推荐：mysql数据库(视频)
The above is the detailed content of MySQL introduces date functions. For more information, please follow other related articles on the PHP Chinese website!