MySQL学习笔记八：日期/时间的处理
浏览数：25 /
时间：2015年06月12日
MySQL日期时间的处理,在其官网文档上都有详细的阐述，想了解更多的同学可自行查阅。
1.查询当前日期时间：函数有now(),localtime(),current_timestamp(),sysdate()。
mysql> select now(),localtime(),current_timestamp(),sysdate(); +---------------------+---------------------+---------------------+---------------------+ | now() | localtime() | current_timestamp() | sysdate() | +---------------------+---------------------+---------------------+---------------------+ | 2015-04-16 09:29:18 | 2015-04-16 09:29:18 | 2015-04-16 09:29:18 | 2015-04-16 09:29:18 | +---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
但是now()与sysdate()有点差异的，一个语句中now()的值是不变的，而sysdate()是动态获取的，例如
mysql> select now(),sleep(2),now(); +---------------------+----------+---------------------+ | now() | sleep(2) | now() | +---------------------+----------+---------------------+ | 2015-04-16 09:34:30 | 0 | 2015-04-16 09:34:30 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) mysql> select sysdate(),sleep(2),sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(2) | sysdate() | +---------------------+----------+---------------------+ | 2015-04-16 09:35:15 | 0 | 2015-04-16 09:35:17 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) -----有此结果可以看出，一般在生成环境中最好使用now()，当然也可以使用sysdate-is-now参数改变sysdate()的行为和now()一样------
2.获取当前日期，curdate()=current_date()=current_date
mysql> select curdate(),current_date(),current_date; +------------+----------------+--------------+ | curdate() | current_date() | current_date | +------------+----------------+--------------+ | 2015-04-16 | 2015-04-16 | 2015-04-16 | +------------+----------------+--------------+ 1 row in set (0.00 sec)
3.获取当前时间，curtime()=current_time()=current_time
mysql> select curtime(),current_time(),current_time; +-----------+----------------+--------------+ | curtime() | current_time() | current_time | +-----------+----------------+--------------+ | 09:42:17 | 09:42:17 | 09:42:17 | +-----------+----------------+--------------+ 1 row in set (0.00 sec)
4.获取UTC日期时间，utc_date(),utc_time(),utc_timestamp()，中国属于东八区，故+8小时即可
mysql> select utc_date(),utc_time(),utc_timestamp(); +------------+------------+---------------------+ | utc_date() | utc_time() | utc_timestamp() | +------------+------------+---------------------+ | 2015-04-16 | 01:55:23 | 2015-04-16 01:55:23 | +------------+------------+---------------------+ 1 row in set (0.00 sec)
5.获取年，月，日
mysql> select year(now()),month(now()),day(now()); +-------------+--------------+------------+ | year(now()) | month(now()) | day(now()) | +-------------+--------------+------------+ | 2015 | 4 | 16 | +-------------+--------------+------------+ 1 row in set (0.00 sec)
6.获取当前星期几，几月，以英文返回，dayname(),monthname()
mysql> select dayname(curdate()),monthname(curdate()); +--------------------+----------------------+ | dayname(curdate()) | monthname(curdate()) | +--------------------+----------------------+ | Thursday | April | +--------------------+----------------------+ 1 row in set (0.03 sec)
7.获取某个日期在周，月，年中的位置，dayofweek(),dayofmonth,dayofyear()，如要返回中文周几，可以在程序中进行逻辑
mysql> set @d=now(); Query OK, 0 rows affected (0.03 sec) mysql> select dayofweek(@d),dayofmonth(@d),dayofyear(@d); +---------------+----------------+---------------+ | dayofweek(@d) | dayofmonth(@d) | dayofyear(@d) | +---------------+----------------+---------------+ | 5 | 16 | 106 | +---------------+----------------+---------------+ 1 row in set (0.03 sec)
8.获取一个月的最后一天，last_day()，利用它可以得到某个月有多少天
mysql> select last_day(@d),day(last_day(@d)); +--------------+-------------------+ | last_day(@d) | day(last_day(@d)) | +--------------+-------------------+ | 2015-04-30 | 30 | +--------------+-------------------+ 1 row in set (0.00 sec)
9.获取某天位于一年中的第N周week(date,3)=weekofyear()，week()函数的第二个参数用来设定以星期几做为一周的开始
mysql> select week(@d,3),weekofyear(@d); +------------+----------------+ | week(@d,3) | weekofyear(@d) | +------------+----------------+ | 16 | 16 | +------------+----------------+ 1 row in set (0.00 sec)
10.获取两个日期或者两个时间的相差，datediff(),timediff()
mysql> select datediff(curdate(),‘2015-02-15‘),timediff(curtime(),‘09:09:09‘) +----------------------------------+--------------------------------+ | datediff(curdate(),‘2015-02-15‘) | timediff(curtime(),‘09:09:09‘) | +----------------------------------+--------------------------------+ | 60 | 01:21:20 | +----------------------------------+--------------------------------+ 1 row in set (0.05 sec)
11.为日期加上或减去一个间隔，date_add(),date_sub()
mysql> select date_add(curdate(),interval 1 day),date_sub(curdate(),interval 1 day); +------------------------------------+------------------------------------+ | date_add(curdate(),interval 1 day) | date_sub(curdate(),interval 1 day) | +------------------------------------+------------------------------------+ | 2015-04-17 | 2015-04-15 | +------------------------------------+------------------------------------+ 1 row in set (0.03 sec) mysql> select date_add(@d,interval ‘01:15:09‘ hour_second),date_sub(@d,interval ‘01:15:09‘ hour_second); +----------------------------------------------+----------------------------------------------+ | date_add(@d,interval ‘01:15:09‘ hour_second) | date_sub(@d,interval ‘01:15:09‘ hour_second) | +----------------------------------------------+----------------------------------------------+ | 2015-04-16 11:21:42 | 2015-04-16 08:51:24 | +----------------------------------------------+----------------------------------------------+ 1 row in set (0.00 sec)
12.时间与秒的转换，time_to_sec(),sec_to_time()
mysql> select time_to_sec(@d),sec_to_time(12389); +-----------------+--------------------+ | time_to_sec(@d) | sec_to_time(12389) | +-----------------+--------------------+ | 36393 | 03:26:29 | +-----------------+--------------------+ 1 row in set (0.00 sec)
13.日期与天数的转换，to_days(),from_days()
mysql> select to_days(@d),from_days(1460000); +-------------+--------------------+ | to_days(@d) | from_days(1460000) | +-------------+--------------------+ | 736069 | 3997-05-06 | +-------------+--------------------+ 1 row in set (0.00 sec)
14.字符串转换为日期，str_to_date(date,format)
mysql> select str_to_date(‘09/09/20‘,‘%Y/%m/%d‘); +------------------------------------+ | str_to_date(‘09/09/20‘,‘%Y/%m/%d‘) | +------------------------------------+ | 2009-09-20 | +------------------------------------+ 1 row in set (0.00 sec) mysql> select str_to_date(‘09.09.20‘,‘%Y.%m.%d‘); +------------------------------------+ | str_to_date(‘09.09.20‘,‘%Y.%m.%d‘) | +------------------------------------+ | 2009-09-20 | +------------------------------------+ 1 row in set (0.00 sec)
format标志含义：
%a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week %V Week (01..53), where Sunday is the first day of the week; used with %X %v Week (01..53), where Monday is the first day of the week; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal “%” character %x x, for any “x” not listed above
15.日期格式化，date_format(str,format)
mysql> select date_format(‘09.09.20‘,‘%Y:%m:%d‘); +------------------------------------+ | date_format(‘09.09.20‘,‘%Y:%m:%d‘) | +------------------------------------+ | 2009:09:20 | +------------------------------------+ 1 row in set (0.00 sec)
16.日期/时间拼凑，makedate(year,dayofyear),maketime(hour,minute,second)
mysql> select makedate(2015,200),maketime(13,20,15); +--------------------+--------------------+ | makedate(2015,200) | maketime(13,20,15) | +--------------------+--------------------+ | 2015-07-19 | 13:20:15 | +--------------------+--------------------+ 1 row in set (0.00 sec)
17.unix时间戳，时间戳转换等，unix_timestamp(date),timestamp(date)
mysql> select unix_timestamp(),unix_timestamp(‘2009-09-09‘),timestamp(now()); +------------------+------------------------------+---------------------+ | unix_timestamp() | unix_timestamp(‘2009-09-09‘) | timestamp(now()) | +------------------+------------------------------+---------------------+ | 1429153960 | 1252425600 | 2015-04-16 11:12:40 | +------------------+------------------------------+---------------------+ 1 row in set (0.00 sec)
18.在应用中碰到需要比较日期时，比如获取某一天的数据，不能使用“=”等于号来比较，使用"<>"，比如
select * from users where regDate<‘2009-10-23‘ and regDate>=‘2009-10-22‘ //如果使用regDate=‘2009-10-22‘相当于00:00:00
19.知道用户的生日得到年龄
mysql> select year(curdate())-year(‘1990-07-30‘)-(right(curdate(),5)<right(‘1990-07-30‘,5)) as age; +------+ | age | +------+ | 24 | +------+ 1 row in set (0.05 sec)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
