今天在业务层面遇到一个问题,我们以往如果在mysql中有个字段的格式是datetime,我们需要这个字段做筛选项的话,会这么写
if startTime != 0 {
db = db.Where("unix_timestamp(created_at) >= ?", startTime)
}
if endTime != 0 {
db = db.Where("unix_timestamp(created_at) <= ?", endTime)
}
可以看到我们其实是将数据库中的该字段从datetime转成了时间戳,然后和我们筛选的时间戳进行比较
在mysql中时间格式的字段使用datetime格式本来就是考虑到了一些时间字段可能会有1970年之前的值,比如生日birthday这样的,而我们今天遇到的问题就是
- mysql不接受负值的时间戳
- 即使字段格式为datetime,unix_timestamp(datetime)转化成时间戳后,时间戳还是从1970年开始的
图中可以很明显地看出,1970年之前的时间戳都是0,哪怕这个字段能够记录1800年之后的时间
这就使得当我们的筛选项需要用到1970年之前的时间的时候,上面那段代码将无法正常进行筛选
通过查百度得知,如果想要在mysql中使用负值时间戳,可以用
DATE_ADD(FROM_UNIXTIME(0), INTERVAL 时间戳 SECOND)
来实现,比如1800年1月1日早上8点
也就是说,用这种写法,可以将时间戳,包括负值时间戳,转化为mysql的datetime格式,然后我们就可以直接和字段值进行比较辣
SELECT * FROM `表名` WHERE birthday <= DATE_ADD(FROM_UNIXTIME(0), INTERVAL 时间戳 SECOND);
在代码中的写法就要改成
if startTime != 0 {
db = db.Where("birthday >= DATE_ADD(FROM_UNIXTIME(0), INTERVAL ? SECOND)", startTime)
}
if endTime != 0 {
db = db.Where("birthday <= DATE_ADD(FROM_UNIXTIME(0), INTERVAL ? SECOND)", endTime)
}
实际执行逻辑就变成将我们传入的时间戳转换为datetime格式,然后直接与字段值进行比较
测试通过,盖姆欧瓦