SQL中将时间戳转为格式化日期后进行比较日期的方法

最近同事用到了这类sql，记录如下：

 

1.首先，数据库表userinfo中，有一个updatetime字段，该字段的格式是bigint(最大长度20)，保存的是13位的时间戳，例如：

updatetime

1610467200666

 

2.要在查询时把这个字段转成格式化日期，可以使用from_unixtime()方法，如下：

select from_unixtime(round(updatetime/1000,0)) from userinfo;

这句的意思是先把updatetime/1000，然后使用round()方法转为四舍五入后含有0位小数的bigint，然后使用from_unixtime()方法将bigint转为格式化日期。

使用round并除以1000的意思是，由于数据库中保存的是13位的时间戳，因此要先转成秒，然后再用from_unixtime，否则无法返回正确结果。

返回的结果样例：

//没有加列名，所以列名默认就这么长了
from_unixtime(round(upd...
2021-01-13 00:00:01
2021-01-12 01:00:01
2021-01-11 02:00:01
2021-01-10 03:00:01
...

3.这个格式化日期并不是想要的格式，所以继续用DATE_FORMAT方法，继续转换格式，如下：

select DATE_FORMAT(from_unixtime(round(updatetime/1000,0)),'%Y-%m-%d') from userinfo;

这次增加了DATE_FORMAT方法，注意使用的是'%Y-%m-%d'，大写的Y，所以结果如下：

DATE_FORMAT(from_unix...
2021-01-13
2021-01-11
...

如果使用'%y-%m-%d'，小写的y，则结果如下：

DATE_FORMAT(from_unix...
21-01-13
21-01-11
...

 

4.明白了如何把时间戳转为格式化日期后，就进行最后一步、实现需求了。

例如，查询最近2天的数据，updateTime是时间戳，不太好比较时，可以用下方的sql：

select curdate() - interval 3 day, DATE_FORMAT(from_unixtime(round(a.updatetime/1000,0)), a.* 
from userinfo a
where DATE_FORMAT(from_unixtime(round(a.updatetime/1000,0)),'%Y-%m-%d') > (curdate() - interval 3 day);

这句sql中，curdate() - interval 3 day 得到的结果是，当前时间减去3天的时间；

然后查询字段updatetime(时间戳格式的时间)大于前3天的数据，也就是最近2天的时间；

开头例子中只用a.*即可，多余的2个是为了显示结果方便一些。

注意，date_format()方法中，要使用'%Y-%m-%d'，大写Y，然后日期格式才正确，才能正确使用大于小于号比较时间！

（如果使用小写y，'%y-%m-%d'，会是【21-01-13 > 2021-01-10】的比较，会存在问题）

结果样例如下：

//第一行是列名，因为没有起，所以都是默认的列名;省略不重要的列
curdate()-interval 3 day   DATE_FORMAT(from_unix...      username  phone   updatetime
2021-01-10                 2021-01-13                     小明     188...   1610467200666

 

5.如果要在sql中将格式化日期转为时间戳，可以使用UNIX_TIMESTAMP()方法，简述如下：

数据库表userinfo中，synctime字段，格式为datetime。

使用sql，可以转化为长度10位的时间戳，意思是单位为秒。

select UNIX_TIMESTAMP(synctime) from userinfo;

结果样例如下：

//第一行是列名，第二行是结果
UNIX_TIMESTAMP(syncti...
1610478685
......