MySQL时间处理函数整理

最近时不时地需要写一些数据查询的sql脚本,其中经常用到各种时间处理函数,每次都打开MySQL手册查阅,甚是麻烦。现将其整理一下。

在数据库里存放时间的方法一般可分为字符串、timestamp、datetime和bigint(unix timestamp 经典的“1970-01-01 0:00 UTC”。而我们用到的时间处理函数无非就是在这些类型中转换,或者提取某一个字段(比如月、日等)。

其实timestamp和datetime可以看成是有特定格式的字符串,两者的使用方法基本一样(时间范围不同)。下面就都用timestamp来举例。

如果要从一个非标准格式的字符串转换成timestamp的话,可以使用str_to_date,比如str_to_date(str, “%d/%m/%Y %H:%i:%s”);反过来,若将timestamp格式化成其他格式,可使用date_format(date, “%d/%m/%Y %H:%i:%s”)。

如果要从timestamp转换成距离UTC 1970年1月1日0点0分的秒数的话,可使用unix_timestamp(date),省略参数表示当前时间;反过来操作的话使用from_unixtime(seconds)

要注意的是,Java中的System.currentTimeMillis()得到的时间含义与此(unix timestamp)相同,但是单位是毫秒。

若要提取时间的某一个字段,则可以使用date_format,在格式中仅写某个值。

下面看一些例子。

假设我们需要将字符串”5/25/2010 13.34.33”转换成Java中System.currentTimeMillis()等效的值。首先,该字符串不是timestamp标准格式,所以需要先使用str_to_date转换一下。然后可使用unix_timestamp转换成秒数,最后变成毫秒数。也就是:

1
unix_timestamp(str_to_date("5/25/2010 13.34.33", "%d/%m/%Y %H.%i.%s")) * 1000

再比如说,数据库里面payment表的time字段记录的是时间毫秒数,而现在需要按月统计支付金额(amount),那么可以这么做:

1
select date_format(from_unixtime(time/1000),"%Y/%m") as mon, sum(amount) from payment group by mon

当然,这只是一种粗略的方法,实际上可能还需要考虑时区的影响。

MySQL中时间相关函数还有很多,这里只是举了一些常用的例子。详细的函数列表和用法可参考官方手册:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html