Mysql中处理1970年前的日期(unixtime为负数的情况)负数时间戳格式化
客户扔过来一个bug,说是一个系统中对42岁以上的人的统计不正确,而41岁以下的人没有问题。眼睛瞟了一下托盘区里的日期,2012年3月26日,嗯,今年42岁的话,那么应该就是出生在1970年左右,马上就把问题锁定在了unixtime上,嗯,重点怀疑!
小贴士:UNIX时间,或称POSIX时间是UNIX或类UNIX系统使用的时间表示方式:从协调世界时1970年1月1日0时0分0秒起至现在的总秒数,不包括闰秒。——来自《维基百科》
看了一下数据库,生日是以int形式保存的,有正有负。这没啥问题嘛。
再看代码,SQL语句中有一个类似下面的条件语句:
WHERE FROM_UNIXTIME(birthday, '%Y') = XXX
其中的birthday是存放生日日期的unixtime值的字段,int类型的。XXX由PHP的date('Y', timestamp)来算出年份。在我的印象中,PHP的date函数处理负数的时间戳是没有问题的(注:PHP4在windows下不能处理负数时间戳,PHP5则没有这个问题。客户的服务器是Linux的,PHP4也没有问题)。好吧,那么我就开始怀疑FROM_UNIXTIME函数了,以前还真没怎么注意这玩意。随手写两个测试:
> SELECT FROM_UNIXTIME(1); > 1970-01-01 08:00:01
看来FROM_UNIXTIME还与时区有关系。继续来:
> SELECT FROM_UNIXTIME(-1); > NULL
哦耶,FROM_UNIXTIME果然不支持负数。测试版本为Mysql 5.1.x
这是一个条件语句,又不能拿出来由PHP处理。当然办法还是很多,比如把生日所在年换一个以unixtime表示的年龄段等等。但是我们只讨论如何用mysql计算负数的unixtime。
我们知道了unixtime表示的是从1970年1月1日0时0分0秒开始到现在的秒数,而为负的情况就是1970年第一天之前的秒数,那么可不可以通过时间计算来算出来呢?查手册,找到一个date_add函数,可以计算时间的加减。那我们就以1970年的第一天(unixtime=0的时候)作为基准来手动计算吧。由于Mysql的日期计算基本上都和时区有关系,我也懒得去搞了,就写一个大一点的秒数来看看结果:
> SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL 1234567 SECOND); > 1970-01-15 14:56:07
这是没有问题的,unixtime为负数的情况下:
> SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -1234567 SECOND); > 1969-12-18 01:03:53
哦耶!成功了!那么剩下的问题就简单了,将本文开始的查询条件修改为:
WHERE DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL birthday SECOND),'%Y') = XXX
就没问题了,BUG去除成功。
本例中用了到INTERVAL关键字,由于它不止可以用于date_add函数,那下面再总结一下DATE_ADD函数的单位(从官方手册里抄来的,为了方便理解,有一点小修改):
使用格式:INTERVAL expr type
举例:
DATE_ADD(date,INTERVAL
expr type)
DATE_SUB(date,INTERVAL expr type)
date +/-INTERVAL expr type等。
关键词INTERVA及
type分类符均不区分大小写。
type值 | 预期的expr格式 |
---|---|
MICROSECOND | 整数 |
SECOND | 整数 |
MINUTE | 整数 |
HOUR | 整数 |
DAY | 整数 |
WEEK | 整数 |
MONTH | 整数 |
QUARTER | 整数(季节) |
YEAR | 整数 |
SECOND_MICROSECOND | '秒.毫秒' |
MINUTE_MICROSECOND | '分钟数.毫秒' |
MINUTE_SECOND | '分:秒' |
HOUR_MICROSECOND | '小时数.毫秒' |
HOUR_SECOND | '时:分:秒' |
HOUR_MINUTE | '时:分' |
DAY_MICROSECOND | '日期号数.毫秒' |
DAY_SECOND | '日 时:分:秒' |
DAY_MINUTE | '日 时:分' |
DAY_HOUR | '日 小时' |
YEAR_MONTH | '年-月' |
相关文章
- mysql 设置 row格式binlog_为什么要把MySQL的binlog格式修改为row
- 报错:MySQL Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
- 实战演练 | 在 MySQL 中计算每日平均日期或时间间隔
- 在 MySQL 中处理日期和时间 - 第五章节
- LINUX上MYSQL优化三板斧
- 【MySQL】常用监控指标及监控方法
- MySQL 日期
- mysql只更新日期不更新时分秒,Mysql取30天内每天最大的数据
- MySQL慢查询 - 开启慢查询
- python操作mysql数据库系列-操作MySql数据库(二)
- Mysql命令mysql:连接Mysql数据库
- Mysql的mvcc版本事务控制
- mysql-日期时间函数大全
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- MySQL数据库:存储引擎
- 在centos服务器安装MySQL数据库详细步骤
- 12:Web及MySQL服务异常监测案例
- 分享MSSQL、MySql、Oracle的大数据批量导入方法及编程手法细节
- MySQL:日期函数、时间函数总结
- MYSQL --延时同步-恢复数据
- MySQL性能优化的21个最佳实践 和 mysql使用索引
- MySql_安装及简单命令
- 【MySql 】is not allowed to connect to this MySql server 无法访问远程MySQL数据库
- mysql插入中文字符报错的问题ERROR 1366 (HY000): Incorrect string value: ‘xE5xB0x8FxE6x98x8E‘ for column
- 使用mysqlbinlog从二进制日志文件中查询mysql执行过的sql语句 (原)
- MySQL 函数 && 单行函数