mysql常用日期时间/数值函数详解(必看)
1.日期时间函数
时间转化秒函数:time_to_sec
MySQL>selecttime_to_sec('01:01:01');
+-------------------------+
|time_to_sec('01:01:01')|
+-------------------------+
|3661|
+-------------------------+
1rowinset(0.00sec)
秒转化时间函数:sec_to_time
mysql>selectsec_to_time(3661);
+-------------------+
|sec_to_time(3661)|
+-------------------+
|01:01:01|
+-------------------+
1rowinset(0.00sec)
日期转为天数函数:to_days
mysql>selectto_days('0000-00-00');
+-----------------------+
|to_days('0000-00-00')|
+-----------------------+
|NULL|
+-----------------------+
1rowinset,1warning(0.01sec)
mysql>selectto_days('0001-01-01');
+-----------------------+
|to_days('0001-01-01')|
+-----------------------+
|366|
+-----------------------+
1rowinset(0.00sec)
天数转化日期函数:from_days
mysql>selectfrom_days(0);
+--------------+
|from_days(0)|
+--------------+
|0000-00-00|
+--------------+
1rowinset(0.00sec)
mysql>selectfrom_days(366);
+----------------+
|from_days(366)|
+----------------+
|0001-01-01|
+----------------+
1rowinset(0.00sec)
字符串转换为日期函数:str_to_date
mysql>selectstr_to_date('2013-01-0101:21:01','%Y-%m-%d%H:%i:%s');
+--------------------------------------------------------+
|str_to_date('2013-01-0101:21:01','%Y-%m-%d%H:%i:%s')|
+--------------------------------------------------------+
|2013-01-0101:21:01|
+--------------------------------------------------------+
1rowinset(0.00sec)
日期转换为字符串函数:date_format
mysql>selectdate_format('2013-01-0101:21:01','%Y%m%d%H%i%s');
+----------------------------------------------------+
|date_format('2013-01-0101:21:01','%Y%m%d%H%i%s')|
+----------------------------------------------------+
|20130101012101|
+----------------------------------------------------+
1rowinset(0.00sec)
时间转换为字符串函数:time_format
mysql>selecttime_format('01:21:01','%H%i%s');
+----------------------------------+
|time_format('01:21:01','%H%i%s')|
+----------------------------------+
|012101|
+----------------------------------+
1rowinset(0.00sec)
说明:
日期时间格式参数如下:
%M月名字(January……December)
%W星期名字(Sunday……Saturday)
%D有英语前缀的月份的日期(1st,2nd,3rd,等等。)
%Y年,数字,4位
%y年,数字,2位
%a缩写的星期名字(Sun……Sat)
%d月份中的天数,数字(00……31)
%e月份中的天数,数字(0……31)
%m月,数字(01……12)
%c月,数字(1……12)
%b缩写的月份名字(Jan……Dec)
%j一年中的天数(001……366)
%H小时(00……23)
%k小时(0……23)
%h小时(01……12)
%I小时(01……12)
%l小时(1……12)
%i分钟,数字(00……59)
%r时间,12小时(hh:mm:ss[AP]M)
%T时间,24小时(hh:mm:ss)
%S秒(00……59)
%s秒(00……59)
%pAM或PM
%w一个星期中的天数(0=Sunday……6=Saturday)
%U星期(0……52),这里星期天是星期的第一天
%u星期(0……52),这里星期一是星期的第一天
%%一个文字“%”。
提取表达式的日期部分
mysql>selectdate(now());
+-------------+
|date(now())|
+-------------+
|2013-05-16|
+-------------+
1rowinset(0.00sec)
返回表达式的星期索引(0=星期一,1=星期二,……6=星期天)。
mysql>selectweekday(now());
+----------------+
|weekday(now())|
+----------------+
|3|
+----------------+
1rowinset(0.00sec)
返回表达式是一年的第几周
mysql>selectweek(now());
+-------------+
|week(now())|
+-------------+
|19|
+-------------+
1rowinset(0.00sec)
WEEK()允许指定星期是否开始于星期天或星期一。如果第二个参数是0,星期从星期天开始,如果第二个参数是1,从星期一开始,如下所示:
mysql>selectweek(now(),0);
+---------------+
|week(now(),0)|
+---------------+
|19|
+---------------+
1rowinset(0.00sec)
mysql>selectweek(now(),1);
+---------------+
|week(now(),1)|
+---------------+
|20|
+---------------+
1rowinset(0.00sec)
返回表达式一年中季度
mysql>selectquarter(now());
+----------------+
|quarter(now())|
+----------------+
|2|
+----------------+
1rowinset(0.00sec)
返回表达式一周的第一天
mysql>selectdayofweek(now());
+------------------+
|dayofweek(now())|
+------------------+
|5|
+------------------+
1rowinset(0.00sec)
返回表达式一个月的第几天
mysql>selectdayofmonth(now());
+-------------------+
|dayofmonth(now())|
+-------------------+
|16|
+-------------------+
1rowinset(0.00sec)
返回表达式一年的第几天
mysql>selectdayofyear(now());
+------------------+
|dayofyear(now())|
+------------------+
|136|
+------------------+
1rowinset(0.00sec)
返回表达式的星期名字
mysql>selectdayname(now());
+----------------+
|dayname(now())|
+----------------+
|Thursday|
+----------------+
1rowinset(0.00sec)
返回表达式月份的名字
mysql>selectmonthname(now());
+------------------+
|monthname(now())|
+------------------+
|May|
+------------------+
1rowinset(0.00sec)
mysql>
提取表达式的年份
mysql>selectyear(now());
+-------------+
|year(now())|
+-------------+
|2013|
+-------------+
1rowinset(0.00sec)
提取表达式的月份
mysql>selectmonth(now());
+--------------+
|month(now())|
+--------------+
|5|
+--------------+
1rowinset(0.01sec)
提取表达式的天数
mysql>selectday(now());
+------------+
|day(now())|
+------------+
|16|
+------------+
1rowinset(0.00sec)
提取表达式的小时
mysql>selecthour(now());
+-------------+
|hour(now())|
+-------------+
|16|
+-------------+
1rowinset(0.00sec)
提取表达式的分钟
mysql>selectminute(now());
+---------------+
|minute(now())|
+---------------+
|31|
+---------------+
1rowinset(0.00sec)
提取表达式的秒数
mysql>selectsecond(now());
+---------------+
|second(now())|
+---------------+
|34|
+---------------+
1rowinset(0.00sec)
将当前日期按照'YYYY-MM-DD'或YYYYMMDD格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
mysql>selectcurdate();
+------------+
|curdate()|
+------------+
|2013-05-16|
+------------+
1rowinset(0.00sec)
mysql>selectcurdate()+1;
+-------------+
|curdate()+1|
+-------------+
|20130517|
+-------------+
1rowinset(0.00sec)
将当前时间以'HH:MM:SS'或HHMMSS的格式返回,具体格式根据函数用在字符串或是数字语境中而定。
mysql>selectcurtime();
+-----------+
|curtime()|
+-----------+
|16:43:10|
+-----------+
1rowinset(0.00sec)
mysql>selectcurtime()+1;
+---------------+
|curtime()+1|
+---------------+
|164420.000000|
+---------------+
1rowinset(0.00sec)
获取当前日期时间:sysdate(),now()
mysql>selectsysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
|sysdate()|sleep(2)|sysdate()|
+---------------------+----------+---------------------+
|2013-05-1617:16:04|0|2013-05-1617:16:06|
+---------------------+----------+---------------------+
1rowinset(2.00sec)
mysql>selectnow(),sleep(2),now();
+---------------------+----------+---------------------+
|now()|sleep(2)|now()|
+---------------------+----------+---------------------+
|2013-05-1617:16:18|0|2013-05-1617:16:18|
+---------------------+----------+---------------------+
1rowinset(2.00sec)
从上面可以看到sysdate和now的区别,now表示语句开始的时间,而sysdate实时的获取时间
将当前日期按照'YYYY-MM-DDHH:MM:SS'或YYYYMMDDHHMMSS格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
mysql>selectcurrent_timestamp;
+---------------------+
|current_timestamp|
+---------------------+
|2013-05-1617:19:51|
+---------------------+
1rowinset(0.00sec)
mysql>selectcurrent_timestamp+1;
+-----------------------+
|current_timestamp+1|
+-----------------------+
|20130516172008.000000|
+-----------------------+
1rowinset(0.00sec)
unix_timestamp(),unix_timestamp(date)
如果没有参数调用,返回一个Unix时间戳记(从'1970-01-0100:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一
个date参数被调用,它返回从'1970-01-0100:00:00'GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME
字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。
mysql>selectunix_timestamp();
+------------------+
|unix_timestamp()|
+------------------+
|1368696216|
+------------------+
1rowinset(0.00sec)
mysql>selectunix_timestamp('2013-05-1601:01:01');
+---------------------------------------+
|unix_timestamp('2013-05-1601:01:01')|
+---------------------------------------+
|1368637261|
+---------------------------------------+
1rowinset(0.00sec)
mysql>
FROM_UNIXTIME(unix_timestamp)
以'YYYY-MM-DDHH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值,具体格式根据函数用在字符串或是数字语境中而定
mysql>selectfrom_unixtime(1368637261);
+---------------------------+
|from_unixtime(1368637261)|
+---------------------------+
|2013-05-1601:01:01|
+---------------------------+
1rowinset(0.00sec)
mysql>selectfrom_unixtime(1368637261)+1;
+-----------------------------+
|from_unixtime(1368637261)+1|
+-----------------------------+
|20130516010102.000000|
+-----------------------------+
1rowinset(0.00sec)
mysql>selectfrom_unixtime(1368637261,'%Y-%m-%d%h:%i:%s');
+-----------------------------------------------+
|from_unixtime(1368637261,'%Y-%m-%d%h:%i:%s')|
+-----------------------------------------------+
|2013-05-1601:01:01|
+-----------------------------------------------+
1rowinset(0.00sec)
返回表达式所在月的最后一天
mysql>selectlast_day(now());
+-----------------+
|last_day(now())|
+-----------------+
|2013-05-31|
+-----------------+
1rowinset(0.00sec)
日期加减运算
DATE_ADD(date,INTERVALexprtype)--加法
DATE_SUB(date,INTERVALexprtype)--减法
mysql>selectdate_add('2013-05-1601:01:01',interval1second);
+---------------------------------------------------+
|date_add('2013-05-1601:01:01',interval1second)|
+---------------------------------------------------+
|2013-05-1601:01:02|
+---------------------------------------------------+
1rowinset(0.00sec)
mysql>selectdate_add('2013-05-1601:01:01',interval1day);
+------------------------------------------------+
|date_add('2013-05-1601:01:01',interval1day)|
+------------------------------------------------+
|2013-05-1701:01:01|
+------------------------------------------------+
1rowinset(0.00sec)
mysql>selectdate_add('2013-05-1601:01:01',interval1minute);
+---------------------------------------------------+
|date_add('2013-05-1601:01:01',interval1minute)|
+---------------------------------------------------+
|2013-05-1601:02:01|
+---------------------------------------------------+
1rowinset(0.00sec)
mysql>selectdate_add('2013-05-1601:01:01',interval1hour);
+-------------------------------------------------+
|date_add('2013-05-1601:01:01',interval1hour)|
+-------------------------------------------------+
|2013-05-1602:01:01|
+-------------------------------------------------+
1rowinset(0.00sec)
mysql>selectdate_add('2013-05-1601:01:01',interval'1:1'minute_second);
+--------------------------------------------------------------+
|date_add('2013-05-1601:01:01',interval'1:1'minute_second)|
+--------------------------------------------------------------+
|2013-05-1601:02:02|
+--------------------------------------------------------------+
1rowinset(0.00sec)
mysql>selectdate_add('2013-05-1601:01:01',interval'11:1:1'day_second);
+----------------------------------------------------------------+
|date_add('2013-05-1601:01:01',interval'11:1:1'day_second)|
+----------------------------------------------------------------+
|2013-05-1702:02:02|
+----------------------------------------------------------------+
1rowinset(0.00sec)
type值格式:
SECOND秒SECONDS
MINUTE分钟MINUTES
HOUR时间HOURS
DAY天DAYS
MONTH月MONTHS
YEAR年YEARS
MINUTE_SECOND分钟和秒"MINUTES:SECONDS"
HOUR_MINUTE小时和分钟"HOURS:MINUTES"
DAY_HOUR天和小时"DAYSHOURS"
YEAR_MONTH年和月"YEARS-MONTHS"
HOUR_SECOND小时,分钟,"HOURS:MINUTES:SECONDS"
DAY_MINUTE天,小时,分钟"DAYSHOURS:MINUTES"
DAY_SECOND天,小时,分钟,秒"DAYSHOURS:MINUTES:SECONDS"
DATEDIFF(expr,expr2)
返回起始时间expr和结束时间expr2之间的天数。Expr和expr2为日期或date-and-time表达式。计算中只用到这些值的日期部分。
mysql>selectdatediff('2013-05-1606:01:01','2013-05-1701:01:01');
+--------------------------------------------------------+
|datediff('2013-05-1606:01:01','2013-05-1701:01:01')|
+--------------------------------------------------------+
|-1|
+--------------------------------------------------------+
1rowinset(0.00sec)
表示日期时间的数据类型:
date
time
year
datetime
timestamp
在使用日期时间数据比较时常用如下
mysql>select*fromtab;
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
|bbbb|2013-04-1417:20:36|
|bbbb|2013-04-1317:20:36|
|bbbb|2013-04-1517:20:36|
+------+---------------------+
4rowsinset(0.00sec)
mysql>selectnow();
+---------------------+
|now()|
+---------------------+
|2013-05-1417:10:26|
+---------------------+
1rowinset(0.00sec)
mysql>select*fromtabwherecreatetime>now();
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
+------+---------------------+
1rowinset(0.00sec)
mysql>selectcurrent_timestamp;
+---------------------+
|current_timestamp|
+---------------------+
|2013-05-1417:10:49|
+---------------------+
1rowinset(0.00sec)
mysql>select*fromtabwherecreatetime>current_timestamp;
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
+------+---------------------+
1rowinset(0.00sec)
mysql>select*fromtabwherecreatetime>str_to_date('2013-05-1400:00:00','%Y-%m-%d%H:%i:%s');;
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
+------+---------------------+
1rowinset(0.00sec)
mysql>select*fromtabwherecreatetimebetweenstr_to_date('2013-05-1400:00:00','%Y-%m-%d%H:%i:%s')andstr_to_date('2013-05-1500:00:00','%Y-%m-%d%H:%i:%s')
->;
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
+------+---------------------+
1rowinset(0.00sec)
mysql>select*fromtabwherecreatetimebetween'2013-05-1400:00:00'and'2013-05-1500:00:00';
+------+---------------------+
|name|createtime|
+------+---------------------+
|aaaa|2013-05-1417:20:19|
+------+---------------------+
1rowinset(0.00sec)
mysql>
2.数值函数
ABS(X):返回表达式X的绝对值
mysql>selectabs(-2);
+---------+
|abs(-2)|
+---------+
|2|
+---------+
1rowinset(0.00sec)
FLOOR(X):返回不大于X的最大整数值
mysql>selectfloor(-2.45);
+--------------+
|floor(-2.45)|
+--------------+
|-3|
+--------------+
1rowinset(0.00sec)
MOD(N,M):模操作,返回N被M除后的余数。
mysql>selectmod(3,2);
+----------+
|mod(3,2)|
+----------+
|1|
+----------+
1rowinset(0.00sec)
RAND()/RAND(N):返回一个随机浮点值数a,范围在0到1之间(即,其范围为0≤a≤1.0)。若已指定一个整数参数N,则它被用作种子值,用来产生重复序列。
mysql>selectrand();
+-------------------+
|rand()|
+-------------------+
|0.294932589209576|
+-------------------+
1rowinset(0.00sec)
mysql>selectrand(2);
+-------------------+
|rand(2)|
+-------------------+
|0.655586646549019|
+-------------------+
1rowinset(0.00sec)
ROUND(X)/ROUND(X,D):返回参数X,其值接近于最近似的整数。在有两个参数的情况下,返回X,其值保留到小数点后D位,而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D位,可将D设为负值。
mysql>selectround(2.4);
+-------------+
|round(2.4)|
+-------------+
|2|
+-------------+
1rowinset(0.00sec)
mysql>selectround(2.432,2);
+-----------------+
|round(2.432,2)|
+-----------------+
|2.43|
+-----------------+
1rowinset(0.00sec)
mysql>selectround(12.432,-1);
+-------------------+
|round(12.432,-1)|
+-------------------+
|10|
+-------------------+
1rowinset(0.00sec)
以上就是小编为大家带来的mysql常用日期时间/数值函数详解(必看)全部内容了,希望大家多多支持毛票票~