Oracle 毫秒时间戳

其实很早以前就经常碰到这个问题，就是得到自1970年1月1日以来的秒数。

这个问题很容易解决：

SQL> SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 FROM DUAL;

(SYSDATE-TO_DATE('1970-1-18','YYYY-MM-DDHH24'))*86400
-----------------------------------------------------
1167040878

用当前的时间减去1970年1月1日8时，得到的天数乘以24小时乘以3600秒，得到的结果就是系统时间戳。这里用8时的原因时系统所处时区为东8区。

而同事提的需求是得到这个毫秒值，而且要相对准确的，将上面的结果直接乘1000是不行的。

而且同事已经通过JAVA外部过程的方式实现了，方法类似于：

SQL> create or replace and compile java source named "getcurrenttime" as
2 public class getcurrenttime extends Object
3 {
4 public static long getcurrenttime()
5 {
6 return System.currentTimeMillis();
7 }
8 }
9 /

Java 已创建。

SQL> CREATE OR REPLACE FUNCTION F_GETCURRENTTIME_JAVA RETURN NUMBER AS
2 LANGUAGE JAVA NAME 'getcurrenttime.getcurrenttime() return long';
3 /

函数已创建。

SQL> SET NUMW 14
SQL> SELECT F_GETCURRENTTIME_JAVA FROM DUAL;

F_GETCURRENTTIME_JAVA
---------------------
1167041159125

然后问我在Oracle中有没有实现的方法。

我首先想到的9i新增加的TIMESTAMP数据类型。但是利用TIMESTAMP类型相减后得到的不是NUMBER类型的日期相差的天数，而是INTERVAL DAY TO SECOND类型，将这个类型转化为毫秒值需要使用EXTRACT函数，写了半天，简化后的SQL为：

SQL> WITH TIME AS
2 (SELECT SYSTIMESTAMP(3) - TO_TIMESTAMP('1970-1-1 8', 'YYYY-MM-DD HH24') T FROM DUAL)
3 SELECT EXTRACT(DAY FROM T) * 86400000
4 + EXTRACT(HOUR FROM T) * 3600000
5 + EXTRACT(MINUTE FROM T) * 60000
6 + EXTRACT(SECOND FROM T) * 1000 AS MILLIONS
7 FROM TIME;

MILLIONS
--------------
1167041521843

这个结果和JAVA的实现相比就太复杂了，后来想到可以利用前面计算DATE类型的结果，于是，SQL语句改写为：

SQL> SELECT (TRUNC(SYSDATE, 'MI') - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000
2 + EXTRACT(SECOND FROM SYSTIMESTAMP(3)) * 1000 AS MILLIONS
3 FROM DUAL;

MILLIONS
--------------
1167041665265

写完之后仍然觉得过于复杂，仔细看了一下，发现根本没有必要使用EXTRACT函数，最终SQL语句改写为：

SQL> SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000
2 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS MILLIONS
3 FROM DUAL;

MILLIONS
--------------
1167041794765

其实就是利用了DATE类型的计算结果，将其扩大1000倍之后，加上了SYSTIMESTAMP中的毫秒部分。

最终这个写法的复杂程度已经是可以接受的了，与建立JAVA外部过程比较，使用这个方法似乎还要简单一些。