mysql⽇期函数month_MySQL⽇期函数、时间函数总结
(MySQL5.X)
⼀、获得当前⽇期时间函数
1.1 获得当前⽇期+时间(date + time)函数:now()unix时间戳转换日期格式
selectnow();
#2019-03-22 11:22:56
除了 now() 函数能获得当前的⽇期时间外,MySQL 中还有下⾯的函数:
current_timestamp()current_timestamplocaltime()
localtime
localtimestamp--(v4.0.6)
localtimestamp() --(v4.0.6)
这些⽇期时间函数,都等同于 now()。鉴于 now() 函数简短易记,建议总是使⽤ now() 来替代上⾯列出的函数。
1.2 获得当前⽇期+时间(date + time)函数:sysdate()
sysdate() ⽇期时间函数跟 now() 类似,不同之处在于:now() 在执⾏开始时值就得到了, sysdate() 在函数执⾏时动态得到值。看下⾯的例⼦就明⽩了:
select now(), sleep(3), now();
#2019-03-22 11:27:54 0 2019-03-22 11:27:54
select sysdate(), sleep(3), sysdate();
#2019-03-22 11:28:39 0 2019-03-22 11:28:42
可以看到,虽然中途 sleep 3 秒,但 now() 函数两次的时间值是相同的; sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的:Return the time at which the function executes。
sysdate() ⽇期时间函数,⼀般情况下很少⽤到。
2. 获得当前⽇期(date)函数:curdate()
selectcurdate();
#2019-03-22
其中,下⾯的两个⽇期函数等同于 curdate():
current_date()current_date
3. 获得当前时间(time)函数:curtime()
selectcurtime();
#11:31:17
其中,下⾯的两个时间函数等同于 curtime():
current_time()current_time
4. 获得当前 UTC ⽇期时间函数:utc_date(), utc_time(), utc_timestamp()
selectutc_timestamp(), utc_date(), utc_time(), now();
#2019-03-22 03:32:00 2019-03-22 03:32:00 2019-03-22 11:32:00
因为我国位于东⼋时区,所以本地时间 = UTC 时间 + 8 ⼩时。UTC 时间在业务涉及多个国家和地区的时候,⾮常有⽤。⼆、⽇期时间 Extract(选取) 函数
1. 选取⽇期时间的各个部分:⽇期、时间、年、季度、⽉、⽇、⼩时、分钟、秒、微秒
set @dt = '2019-03-22 11:34:30.123456';select date(@dt); --2019-03-22
select time(@dt); --11:34:30.123456
select year(@dt); --2019
select quarter(@dt); --1
select month(@dt); --3
select week(@dt); --11
select day(@dt); --22
select hour(@dt); --11
select minute(@dt); --34
select second(@dt); --30
select microsecond(@dt); --123456
2. Extract() 函数,可以实现上⾯类似的功能:
set @dt = '2019-03-22 11:34:30.123456';select extract(year from @dt); --2019
select extract(quarter from @dt); --1
select extract(month from @dt); --3
select extract(week from @dt); --11
select extract(day from @dt); --22
select extract(hour from @dt); --11
select extract(minute from @dt); --34
select extract(second from @dt); --30
select extract(microsecond from @dt); --123456
select extract(year_month from @dt); --201903
select extract(day_hour from @dt); --2211
select extract(day_minute from @dt); --221134
select extract(day_second from @dt); --22113430
select extract(day_microsecond from @dt); --22113430123456
select extract(hour_minute from @dt); --1134
select extract(hour_second from @dt); --113430
select extract(hour_microsecond from @dt); --113430123456
select extract(minute_second from @dt); --3430
select extract(minute_microsecond from @dt); --3430123456
select extract(second_microsecond from @dt); --30123456
Extract() 函数除了没有date(),time() 的功能外,其他功能⼀应具全。并且还具有选取‘day_microsecond’ 等功能。注意这⾥不是只选取 day 和 microsecond,⽽是从⽇期的 day 部分⼀直选取到 microsecond 部分。
3. 函数:dayofweek(), dayofmonth(), dayofyear()
分别返回⽇期参数,在⼀周、⼀⽉、⼀年中的位置。
set @dt = '2019-03-22';select dayofweek(@dt); --6
select dayofmonth(@dt); --22
select dayofyear(@dt); --81
⽇期 '2019-03-22' 是⼀周中的第 6 天(1 = Sunday, 2 = Monday, ..., 7 = Saturday);⼀⽉中的第 22 天;
⼀年中的第 81天。
4. 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = '2018-03-22';select week(@dt); --11
select week(@dt,3); --12
select weekofyear(@dt); --12
select dayofweek(@dt); --5
select weekday(@dt); --3
select yearweek(@dt); --201811
week() 函数,可以有两个参数,具体可看⼿册。 weekofyear() 和 week() ⼀样,都是计算“某天”是位于⼀年中的第⼏周。weekofyear(@dt) 等价于 week(@dt,3)。
weekday() 函数和 dayofweek() 类似,都是返回“某天”在⼀周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, ..., 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, ..., 7
= Saturday)
yearweek() 函数,返回 year(2008) + week 位置(31)。
5. 返回星期和⽉份名称函数:dayname(), monthname()
set @dt = '2019-03-22';select dayname(@dt); --Friday
select monthname(@dt); --March
6. last_day() 函数:返回⽉份中的最后⼀天
select last_day('2008-02-01'); --2008-02-29
select last_day('2019-02-01'); --2019-02-28
select last_day('2019-08-08'); --2019-08-31
last_day() 函数⾮常有⽤,⽐如我想得到当前⽉份中有多少天,可以这样来计算:
select now(), day(last_day(now())) asdays;
#2019-03-22 11:58:52 31
三、⽇期时间计算函数
1. MySQL 为⽇期增加⼀个时间间隔:date_add()
set @dt =now();select date_add(@dt, interval 1 day); --add 1 day
select date_add(@dt, interval 1 hour); --add 1 hour
select date_add(@dt, interval 1 minute); --...
select date_add(@dt, interval 1second);select date_add(@dt, interval 1microsecond);select date_add(@dt, interval
1week);select date_add(@dt, interval 1 month);select date_add(@dt, interval 1quarter);select date_add(@dt, interval 1 year);select date_add(@dt, interval -1 day); --sub 1 day
MySQL adddate(), addtime()函数,可以⽤ date_add() 来替代。
下⾯是 date_add() 实现 addtime() 功能⽰例:
set @dt = '2019-03-22 12:12:33';select date_add(@dt, interval '01:15:30' hour_second); --2019-03-22 13:28:03
select date_add(@dt, interval '1 01:15:30' day_second); --2019-03-23 13:28:03
date_add() 函数,分别为 @dt 增加了“1⼩时 15分 30秒” 和 “1天 1⼩时 15分 30秒”。建议:总是使⽤ date_add() ⽇期时间函数来替代 adddate(), addtime()。
2. MySQL 为⽇期减去⼀个时间间隔:date_sub()
select date_sub('1998-01-01 00:00:00', interval '1 1:1:1'day_second);
#1997-12-30 22:58:59
MySQL date_sub() ⽇期时间函数 和 date_add() ⽤法⼀致,不再赘述。另外,MySQL 中还有两个函数 subdate(), subtime(),建议,⽤ date_sub() 来替代。
3. MySQL 另类⽇期函数:period_add(P,N), period_diff(P1,P2)
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”,第⼆个参数“N” 表⽰增加或减去 N month(⽉)。
MySQL period_add(P,N):⽇期加/减去N⽉。
select period_add(200808,2), period_add(20080808,-2);
#200810 20080806
MySQL period_diff(P1,P2):⽇期 P1-P2,返回 N 个⽉。
select period_diff(200808, 200801);
#7
在 MySQL 中,这两个⽇期函数,⼀般情况下很少⽤到。
4. MySQL ⽇期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
MySQL datediff(date1,date2):两个⽇期相减 date1 - date2,返回天数。
select datediff('2008-08-08', '2008-08-01'); --7
select datediff('2008-08-01', '2008-08-08'); ---7
MySQL timediff(time1,time2):两个⽇期相减 time1 - time2,返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); --08:08:08
select timediff('08:08:08', '00:00:00'); --08:08:08
注意:timediff(time1,time2) 函数的两个参数类型必须相同。
四、⽇期转换函数、时间转换函数
1. (时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05'); --3605
select sec_to_time(3605); --'01:00:05'
2. (⽇期、天数)转换函数:to_days(date), from_days(days)
select to_days('0000-00-00'); --0
select to_days('2008-08-08'); --733627
select from_days(0); --'0000-00-00'
select from_days(733627); --'2008-08-08'
3. Str to Date (字符串转换为⽇期)函数:str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); --2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); --2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); --2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); --08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); --2008-08-09 08:09:30
可以看到,str_to_date(str,format) 转换函数,可以把⼀些杂乱⽆章的字符串转换为⽇期格式。另外,它也可以转换为时间。“format”可以参看 MySQL ⼿册。
4. Date/Time to Str(⽇期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)
select date_format('2008-08-08 22:23:00', '%W %M %Y'); --Friday August 2008
select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); --20080808222301
select time_format('22:23:01', '%H.%i.%s'); --22.23.01
MySQL ⽇期、时间转换函数:date_format(date,format), time_format(time,format) 能够把⼀个⽇期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 ⼀个逆转换。
5. 获得国家地区时间格式函数:get_format()
get_format() 语法:
get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'
get_format() ⽤法的全部⽰例:
select get_format(date,'usa') ; --'%m.%d.%Y'
select get_format(date,'jis') ; --'%Y-%m-%d'
select get_format(date,'iso') ; --'%Y-%m-%d'
select get_format(date,'eur') ; --'%d.%m.%Y'
select get_format(date,'internal') ; --'%Y%m%d'
select get_format(datetime,'usa') ; --'%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis') ; --'%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso') ; --'%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur') ; --'%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ; --'%Y%m%d%H%i%s'

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。