Unix时间戳处理函数

1. 概述
1.1 函数作用

Unix时间戳是以从'1970-01-01 00:00:00' GMT开始的秒数值作为数据存储在数据库中，在 MySQL 中通常使用 FROM_UNIXTIME() 函数将其转化为普通时间。

1.2 函数解释

语法	FROM_UNIXTIME(unix_timestamp,format)	根据 format 字符串的格式转换 unix_timestamp 值
参数1	unix_timestamp	长整型，是以秒为单位的时间戳
参数2	format	日期格式，默认格式为yyyy-MM-dd HH:mm:ss，表示返回 Varchar 类型的符合指定格式的日期，如果有参数为 null 或解析错误，则返回 null

注：format 中的日期格式有特定写法.

%M 月名字(January～December)
%W 星期名字(Sunday～Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字（Sun～Sat）
%d 月份中的天数, 数字（00～31）
%e 月份中的天数, 数字（0～31）
%m 月, 数字（01～12）
%c 月, 数字（1～12）
%b 缩写的月份名字（Jan～Dec）
%j 一年中的天数（001～366）
%H 小时（00～23）
%k 小时（0～23）
%h 小时（01～12）
%I 小时（01～12）
%l 小时（1～12）
%i 分钟, 数字（00～59）
%r 时间,12 小时（hh:mm:ss [AP]M）
%T 时间,24 小时（hh:mm:ss）
%S 秒（00～59）
%s 秒（00～59）
%p AM或PM
%w 一个星期中的天数（0=Sunday ～6=Saturday）
%U 星期（0～52）, 这里星期天是星期的第一天
%u 星期（0～52), 这里星期一是星期的第一天
%% 一个文字%

2. 示例

在数据处理时，不同的数据库有不同的方法将 Unix 时间戳转为普通时间

1）MySQL 处理方式

使用 FROM_UNIXTIME() 函数

SELECT FROM_UNIXTIME(1249488000, '%Y%m%d' )等于 20090806

SELECT FROM_UNIXTIME(1249488000, '%Y年%m月%d' ) 等于 2009年08月06

SELECT FROM_UNIXTIME(875996580) 等于 1997-10-05 04:23:00

SELECT FROM_UNIXTIME(875996580) + 0 等于19971004222300

2）SQL Server 处理方式

使用 DateAdd() 函数

DATEADD(s, Unix_timestamp, '1970-01-01 00:00:00')

SELECT DATEADD(s,1249488000, '1970-01-01 00:00:00')  等于 2009-08-05 16:00:00

注：其中 s 表示秒，Unix_timestamp 表示 Unix时间戳

3）Oracle 处理方式

因为Oracle中没有专门处理 Unix 时间戳的函数，所以可以使用以下方法

代码处理：

SELECT Unix_timestamp / (60 * 60 * 24) +TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL

注：Unix_timestamp 表示 Unix 时间戳

SELECT 1249488000/ (60 * 60 * 24) +TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL 等于 2009-08-06 00:00:00

注：上述的时间返回值类型为date

函数处理：

可在数据库新建函数，之后再遇到 Unix 时间戳时，无需进行上述的SQL进行处理

CREATE OR REPLACE FUNCTION MYSQL_ORCL_DATE  -- FUNCTION 后面为函数名称（MYSQL_ORCL_DATE）
(
  mysql_date in varchar2
)
RETURN DATE IS ORCL_DATE DATE;

BEGIN
  ORCL_DATE := MYSQL_DATE/ (60 * 60 * 24) +
                TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS');
  return ORCL_DATE;
END;

当函数在数据库创建之后，之后的处理SQL可改为：SELECT MYSQL_ORCL_DATE(Unix_timestamp) FROM DUAL

例如：SELECT MYSQL_ORCL_DATE(1249488000)FROM DUAL 等于 2009-08-06 00:00:00

3. 其他

如果对于某一个Unix 时间戳先要快速的转化为普通时间或者想了解实时的Unix 时间戳，可点击该链接：UNIX时间戳转换