MySQL时间与日期函数
- 1、 获取当前日期的函数和获取当前时间的函数
- 2、获取当前日期和时间的函数
- 3、UNIX时间戳函数
- 4、返回UTC日期的函数和返回UTC时间的函数
- 5、获取月份的函数month(date)、monthname(date)
- 6、获取星期的函数dayname(d)、dayofweek(d)、weekday(d)
- 7、获取星期数的函数week(d)、dayofyear(d)
- 8、获取天数的函数dayofyear(d)、dayofmonth(d)
- 9、获取年份、季度、小时、分钟和秒钟的函数
- 10、获取日期的指定值的函数extract(type from date)
- 11、时间和秒钟转换的函数
- 12、计算日期和时间的函数
- 13、将日期和时间格式化的函数
1、 获取当前日期的函数和获取当前时间的函数
(1) curdate()、current_date()
【例】使用日期函数获取系统当前日期,SQL语句如下:
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)
(2)curtime()、current_time()
【例】使用时间函数获取系统当前时间,SQL语句如下:
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表示将当前时间值转换为数值型。
2、获取当前日期和时间的函数
current_timestamp()、localtime()、now()、sysdate()
四个函数作用相同,都是返回当前日期和时间值。
【例】使用日期时间函数获取当前系统时间和日期,SQL语句如下:
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时间戳函数
(1)unix_timestamp(date)
unix_timestamp(date)若为无参数调用,则返回一个unix时间戳(GMT之后的秒数,GMT为格林尼治标准时间1970.1.1)作为无符号整数。
date可以是一个date字符串,datetime字符串、timestamp或一个当地时间的YY[YY]MMDD格式的数字。
【例】使用unix_timestamp函数返回unix格式的时间戳,SQL语句如下:
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)
(2)from_unixtime(date)
from_unixtime()函数把unix时间戳转换为普通格式的时间,与unix_timestamp(date)函数互为反函数。
【例】使用from_unixtime函数将unix时间戳转换为普通格式时间,SQL语句如下:
mysql> select from_unixtime('1566127316');
+-----------------------------+
| from_unixtime('1566127316') |
+-----------------------------+
| 2019-08-18 19:21:56.000000 |
+-----------------------------+
1 row in set (0.00 sec)
4、返回UTC日期的函数和返回UTC时间的函数
(1)UTC_DATE()
【例】使用utc_date()函数返回当前UTC日期值,SQL语句如下:
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()
【例】使用UTC_TIME()函数返回当前UTC时间值,SQL语句如下:
mysql> select utc_time(),utc_time()+0;
+------------+--------------+
| utc_time() | utc_time()+0 |
+------------+--------------+
| 11:32:27 | 113227 |
+------------+--------------+
1 row in set (0.00 sec)
5、获取月份的函数month(date)、monthname(date)
(1)month(date)
【例】使用month()函数返回指定日期中的月份,SQL语句如下:
mysql> select month('2019-08-18');
+---------------------+
| month('2019-08-18') |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)
(2)monthname(date)
【例】使用monthname()函数返回指定日期中的月份名称,SQL语句如下:
mysql> select monthname('2019-08-18');
+-------------------------+
| monthname('2019-08-18') |
+-------------------------+
| August |
+-------------------------+
1 row in set (0.00 sec)
6、获取星期的函数dayname(d)、dayofweek(d)、weekday(d)
(1)dayname(d)
【例】使用dayname()函数返回指定日期的工作日名称,SQL语句如下:
mysql> select dayname('2019-08-18');
+-----------------------+
| dayname('2019-08-18') |
+-----------------------+
| Sunday |
+-----------------------+
1 row in set (0.00 sec)
(2)dayofweek(d)
【例】使用dayofweek()函数返回日期对应的周索引,SQL语句如下:
mysql> select dayofweek('2019-08-18');
+-------------------------+
| dayofweek('2019-08-18') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
(3)weekday(d)
weekday(d)返回d对应的工作日索引:0代表周一,1代代表周二,…6代表周日。
【例】使用weekday()函数返回日期对应的工作日索引,SQL语句如下:
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、获取星期数的函数week(d)、dayofyear(d)
(1)week(d)
week(d)计算日期d是一年中第几周,双参形式允许指定该星期是否起始于周日或周一,若Mode参数被忽略,使用default_week_format系统自变量的值。
【例】使用week()函数查询指定日期是一年中的第几周,SQL语句如下:
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)
【例】使用weekofyear()查询指定日期是一年中的第几周,SQL语句如下:
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、获取天数的函数dayofyear(d)、dayofmonth(d)
(1)dayofyear()
【例】使用dayofyear()函数返回指定日期在一年中的位置,SQL语句如下:
mysql> select dayofyear('2019-08-18');
+-------------------------+
| dayofyear('2019-08-18') |
+-------------------------+
| 230 |
+-------------------------+
1 row in set (0.00 sec)
(2)dayofmonth()
【例】使用dayofmonth()函数返回指定日期在一个月中的位置,SQL语句如下;
mysql> select dayofmonth('2019-08-18');
+--------------------------+
| dayofmonth('2019-08-18') |
+--------------------------+
| 18 |
+--------------------------+
1 row in set (0.00 sec)
9、获取年份、季度、小时、分钟和秒钟的函数
(1)YEAR(date)
【例】使用year()函数返回指定日期对应的年份,SQL语句如下:
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)
【例】使用quarter()函数返回指定日期对应的季度,SQL语句如下:
mysql> select quarter('19-08-18');
+---------------------+
| quarter('19-08-18') |
+---------------------+
| 3 |
+---------------------+
1 row in set (0.00 sec)
(3)MINUTE(time)
【例】使用minute()函数返回指定时间的分钟值,SQL语句如下:
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)
【例】使用second()函数返回指定时间的秒值,SQL语句如下:
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)
【例】使用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、计算日期和时间的函数
(1)date_add(date,interval expr type)、adddate(date,interval expr type)
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(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)
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)
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)
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、将日期和时间格式化的函数
(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)