mysql中时间处理函数_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转换成秒数，最后变成毫秒数。也就是：

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

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

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

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

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