mysql时间相关函数

article/2025/8/25 21:52:07

mysql数据库

1、获取当前时间

select now()    -- 获取当前具体的日期和时间 2022-05-07 16:07:39
select curdate()    -- 获取当前日期 2022-05-07
select curtime()       -- 获取当前时间 16:08:44

select current_timestamp() -- 获取当前具体的日期和时间2022-05-07 16:07:39

2、MySQL加减某个时间间隔

2.1 设置当前日期变量

set @dt = now() -- 设置变量获取当前日期
select @dt -- 查询变量值

2.2 加减某个时间间隔函数date_add()与date_sub()

MySQL adddate(), addtime()函数，可以用date_add() 来替代。

此外，MySQL 为日期减去一个时间间隔：date_sub()
MySQL date_sub() 日期时间函数和date_add() 用法一致。
MySQL 中subdate(),subtime()函数，建议用date_sub()来替代。

date_add('某个日期时间',interval 1 时间种类名);

year:年，quarter:季，month:月，week:周，day:天，hour:小时，minuter:分钟，second:秒，microsecond:毫秒

示例：

select date_add(now(), interval 1 year); //加1年

select date_add(now(), interval 1 hour); -- 加1小时

select date_add('2022-05-07',interval 1 day); -- 加1天

3、日期相减

datediff(date1,date2)：两个日期相减，date1减去date2得到相减之后的天数,精确到天

timediff(time1,time2)：两个时间相减 time1减time2，返回差值时间,精确到了秒。

timestampdiff(unit,datetime_expr1,datetime_expr2) 返回差值unit,精确到了秒。

注：unit为相差的单位year,month,week,day

datediff(date1,date2)：两个日期相减，date1减去date2得到相减之后的天数,精确到unit

select datediff('20191010','20191001')

select create_time,update_time,datediff(create_time,update_time) from tables -- 计算相差的天数

timediff(time1,time2)：两个时间相减 time1减time2，返回差值时间,精确到了秒。

select create_time,update_time,timediff(create_time,update_time) from tables -- 计算相差的时间

timestampdiff(unit,datetime_expr1,datetime_expr2) 返回差值unit,精确到了unit。

select create_time,update_time,timestampdiff(year,create_time,update_time) from tables

select create_time,update_time,timestampdiff(month,create_time,update_time) from tables

select create_time,update_time,timestampdiff(week,create_time,update_time) from tables

select create_time,update_time,timestampdiff(day,create_time,update_time) from tables

select create_time,update_time,TO_DAYS(create_time)-TO_DAYS(update_time) from tables

4、日期和字符相互转换方法
date_format(date,’%Y-%m-%d’) 将时间类型的日期转换成str类型的日期
str_to_date(date,’%Y-%m-%d’) 将str类型的日期转换成时间类型的日期

%Y：代表4位的年份
%y：代表2位的年份

%m：代表月, 格式为(01,02……12)
%c：代表月, 格式为(1,2……12)

%d：代表月份中的天数,格式为(00,01……31)
%e：代表月份中的天数, 格式为(0,1……31)

%H：代表小时,格式为(00,01……23)
%k：代表小时,格式为(0,1……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)

SELECT DATE_FORMAT(20130111191640,’%Y-%m-%d %H:%i:%s’)

5、int时间类型转为datetime类型

select from_unixtime(1506059685,format) 将int类型的时间转换成datetime类型时间,format格式可不加，则为默认的时间格式

select unix_timestamp('2022-05-08 15:40:09') 将时间转换成int类型的时间

select from_unixtime(1651907596) -- 将int类型的时间转换成date类型的时间

select from_unixtime(1651907596,'%Y%m%d %H%i%s') -- 将int类型的时间转换成date类型的时间

select unix_timestamp(now()) -- 将date类型的时间转换成int类型的时间

6、时间类型
mysql有五⼤时间类型：
1. datetime 可以存储年⽉⽇时分秒类型的时间，如果在设计字段的时候可以设置获取当前时间，插⼊数据的时候，如果没有对该赋值，mysql会⾃动获取当前的时间填充
2. timestamp 可以存储年⽉⽇时分秒类型的时间，跟 datetime ⼀样，也可以存储时间戳，timestamp 的精度是14位，存储也是14位，但是如果设置了⾃⼰需要的精度，那么 mysql 会将14位处理成设置的精度进⾏显⽰，底层储仍然是14位精度。timestamp有⼀个⽐较有趣的字段，受当前时趣的字段，受当前时区的影响。如果某条记录的字段被修改（不包括datestamp字段），那么 datestamp 字段可以⾃动更新为数据修改的时间，所以也可以⽤来代替 updated_time 字段（我习惯⽤ updated_time 字段名来记录某条数据被修改的时间，⽅便查看）

在创建新记录和修改某条记录的时候都对该类型的字段更新为当前时间：
TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP
在创建新记录的时候把这个类型的字段设置为当前时间，当以后修改的时候，不再更新：
TIMESTAMPDEFAULTCURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为0，以后修改的时候更新该字段：
TIMESTAMPONUPDATECURRENT_TIMESTAMP
在创建新记录的时候把这个类型的字段字段设置为给定值，以后修改的时候也会更新该字段：
TIMESTAMPDEFAULT 'yyyy-mm-dd hh:mm:ss' ONUPDATECURRENT_TIMESTAMP

3. date 可以存储年⽉⽇时间
4. time 可以存储时分秒时间
5. year 可以存储年份

7、时间⽐较
java中的常⽤的 Date 类本⾝提供了 before， after⽅法⽐较时间的先后，mysql中时间的⽐较灵活，可以直接使⽤ <、>、= 进⾏⽐较，适应所有时间字段，但是格式需要相同，也可以使⽤ between and 来进⾏⽐较。
还有⼀个⽐较灵活的，之前⼯作中，有这么个场景，有⼀个时间字段在 java 是String 类型，需要作为参数到 sql 语句中和时间类型的字段⽐较，⼀般我都是使⽤ MySQL ⾃带的 str_to_date(str, format) 函数将字符串先转化为时间类型再⽐较，后来发现其实时间类型的字段可以和字符串类型的字段直接进⾏⽐较，不⽤转为相同类型，省去了转化的时间，当然时间格式也要⼀样。

timetest的表结构：