MySQL一些常用的时间函数 current_timestamp()

转自：https://my.oschina.net/sallency/blog/470370

mysql自带的一些函数功能也是很强大的，这里整理了一下时间方面的

获取当前时间格式串：

#获取当前时间戳
current_timestamp() yyyy-mm-dd hh:ii:ss 
now() yyyy-mm-dd hh:ii:ss
curdate() yyyy-mm-dd
current_date()
curtime() hh:ii:ss
current_time()

提取date各个字段

#提取date各个字段
date('yyyy-mm-dd hh:ii:ss') yyyy-mm-dd
year('yyyy-mm-dd hh:ii:ss') yyyy
month('yyyy-mm-dd hh:ii:ss') mm
day('yyyy-mm-dd hh:ii:ss') dd

提取time各个字段

#提取time各个字段
time('yyyy-mm-dd hh:ii:ss') hh:ii:ss
hour('yyyy-mm-dd hh:ii:ss') hh
minute('yyyy-mm-dd hh:ii:ss') ii
second('yyyy-mm-dd hh:ii:ss') ss

获取当前或者某一时间的unix时间戳

#unix时间戳1970-01-01以来的秒数
unix_timestamp()
#同时还可以将某一时间格式串的秒数转化出来
unix_timestamp('yyyy-mm-dd hh:ii:ss')

格式化时间串和格式化时间戳

#格式化时间串 date_format 与 time_format 为同一函数 输入为 
date_format(now(), '%Y-%m-%d %T');
time_format(now(), '%H:%i:%s');

#格式化时间戳 将时间戳转化为时间格式串
from_unixtime(unix_timestamp(), "%Y-%m-%d %T")

datetime 类型 与 timestamp 类型的比较

timestamp类型的列可以设置 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 等特殊属性

而 datetime 作为时间格式无法设置当前时间为其默认值，因为mysql不允许函数作为默认值定义，即 default now()是无效的 ，其他的时间格式还有 YEAR DATE TIME

datetime YYYY-mm-dd HH:ii:ss '1000-01-01 00:00:00'到'9999-12-31 23:59:59' 8字节
timestamp YYYY-mm-dd HH:ii:ss '1970-01-01 00:00:00'到'2037-12-31 23:59:59' 4字节

date_add() date_sub() interval

对于时间戳直接加值就可以进行时间平移，但对于时间格式串的加减我们可以使用 INTERVAL expr TYPE

TYPE是指跨度单位：

MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER【四分之一年】 YEAR

还有

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
#"5:20:15" 5小时20分15秒的步进量
HOUR_SECOND
#"5:20" 5小时20分的步进量
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
#"0001-02"1年零2个月的步进量
YEAR_MONTH

#+- 参照点 间隔度
#当前时间加5天
date_add(now(), interval 5 DAY)
#当前时间减5天
date_sub(now(), interval 5 DAY)
#加5天2小时10分钟20秒
date_add(now(), interval "05 02:10:20" DAY_SECOND);
#加1年2个月
date_add(now(), interval "0001-02" YEAR_MONTH);
#当然也可以直接用运算符
select now() + interval 5 DAY, now() - interval 5 DAY
#换成时间戳的运算是
select unix_timestamp() + 5*24*60*60, unix_timestamp() - 5*24*60*60

extract()从时间格式串中提取时间

#提取天
select extract(DAY from now());
#提取天 小时 分钟 秒 
select extract(DAY_SECOND from now());

datediff()返回两个时间点相差的天数

#-5天 只会计算天数
select datediff(now(), now() + interval 5 day);