oracle 获取日期的毫秒_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外部过程比较，使用这个方法似乎还要简单一些。

分享到：

2012-03-10 16:55

浏览 10953

评论