mysql日期时间笔记

捣鼓了一下mysql，做如下笔记

mysql版本：5.1.50

 

一，日期时间类型
datetime 8bytes xxxx-xx-xx xx:xx:xx 1000-01-01 00:00:00到9999-12-31 23:59:59
timestamp 4bytes xxxx-xx-xx xx:xx:xx 1970-01-01 00:00:01到2038
date 3bytes xxxx-xx-xx 1000-01-01到9999-12-31
year 1bytes xxxx 1901到2155
time 3bytes xx:xx:xx -838:59:59到838:59:59(为了满足时间的加减运算)

二，特性
timestamp在默认情况下，insert和update数据时，timestamp列自动回去当前时间填充
mysql只支持到秒

 

 

三，函数

注：current_date[()]表示后面的括号可以有也可以没有

 

获取当前日期或时间

sysdate()       xxxx-xx-xx xx:xx:xx
current_timestamp[()]=now()       xxxx-xx-xx xx:xx:xx
current_date[()]=curdate()       xxxx-xx-xx xx:xx:xx
current_time[()]=curtime()       xx:xx:xx
unix_timestamp()       12xxxxxxxx

 

 

时间日期运算

sysdate()+interval 1 day(hour)       xxxx-xx-xx xx:xx:xx
current_date+interval 1 day       xxxx-xx-xx
current_date+interval 1 hour       xxxx-xx-xx xx:xx:xx
current_time+interval 1 day(hour) null
current_timestamp+interval 1 day(hour)       xxxx-xx-xx xx:xx:xx

 

timediff(a,b)
返回a-b的时间值，适用于datetime/timestamp/time类型，并且a和b类型要一样
datediff(a,b)类似

 

set @dt='2010-09-28 23:05:14'
select minute(@dt) 14
.
.
select dayofweek(@dt) 返回在一周之中位于第几天（1代表周日）
.
.
select dayname(@dt) 返回英文的周几（Friday）
.
.
select date_add(@dt,interval -1 day)
select date_add(@dt,interval '01:15:31' hour_second)
select date_add(@dt,interval '01 01:15:31' day_second)
select date_sub(……类似

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
select date_format('2008-08-08 22:23:00', '%W %M %Y');--Friday August 2008

 

unix时间戳
select unix_timestamp();                       -- 1218290027
select unix_timestamp('2008-08-08');           -- 1218124800
select unix_timestamp('2008-08-08 12:30:00');  -- 1218169800
select from_unixtime(1218169800);              -- '2008-08-08 12:30:00'
select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

 

四，时间输出标记
'2010-09-08 13:14:15'
%W-%w Wednesday-3 
%Y-%y 2010-10
%M-%m September-09
%D-%d 8th-08
%H-%h 13-01
%I-%i 12-13(特别注意，%I=%h)
%S-%s 15-15

五，补充
utc时间+8小时=本地时间，涉及多国领域时再考虑utc时间（cur_timestamp()）