Oracle 将日期转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22432662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle convert Date to Number
提问by muthukumar
I want to convert date to number in oracle 11g .
我想在 oracle 11g 中将日期转换为数字。
The date is stored in (12/03/2013 12:23:00).
日期存储在 (12/03/2013 12:23:00)。
I want to convert this to Number(The date corresponding number value). As in java we convert date to long .
我想将其转换为 Number（日期对应的数值）。就像在 java 中一样，我们将 date 转换为 long 。
I want the same to be done here .
我想在这里做同样的事情。
Calendar c = Calendar.getInstance();
c.set(2013, 05, 23, 0, 0, 0);
Date date = c.getTime();
System.out.println("Date is " + date);
long longDate = date.getTime();
System.out.println("Date as long :" + longDate);
Date d = new Date(longDate);
System.out.println("Converted Date :" + d);*
The Output is :
输出是：
**Date is Sun Jun 23 00:00:00 SGT 2013
**日期为 2013 年 6 月 23 日星期日 00:00:00 SGT
Date as long :1371916800981
日期：1371916800981
Converted Date :Sun Jun 23 00:00:00 SGT 2013**
转换日期：Sun Jun 23 00:00:00 SGT 2013**
Now I want to store value as 1371916800981
现在我想将值存储为 1371916800981
回答by Gordon Linoff
I am guessing that the long data type that you want is something like the number of seconds or milliseconds since 1970-01-01.
我猜测您想要的长数据类型类似于自 1970-01-01 以来的秒数或毫秒数。
To get this requires just a bit of arithmetic:
要得到这个，只需要一点算术：
select (to_date(s1, 'MM/DD/YYYY HH24:MI:SS') -
to_date('1970-01-01', 'YYYY-MM-DD')
) *24*60*60
from (select '12/03/2013 12:23:00' as s1 from dual
) t
I note that your result is using the current time stamp. This might include milliseconds which this constant date format doesn't include.
我注意到您的结果使用的是当前时间戳。这可能包括此常量日期格式不包括的毫秒。
回答by Jakub P
You can achieve this with a help of RAW data type:
您可以借助 RAW 数据类型来实现这一点：
SELECT
s1,
to_number(TO_CHAR(s1,'YYYYMMDDHH24MISS')) as s2,
utl_raw.cast_to_raw(s1) as d1,
utl_raw.cast_to_raw(s2) as d2,
round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)),20) as s1,
round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)),20) as s2,
utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)) - utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)) as s1_s2_diff
FROM
( select
sysdate as s1,
sysdate-1/24/60/60 as s2
from dual );
Output:
输出：
S1 S2
------------------- --------------
2014.03.16 11:14:16 20140316111416
D1 D2
-------------------------------------- --------------------------------------
323031342E30332E31362031313A31343A3136 323031342E30332E31362031313A31343A3135
S1_1 S2_1
-------------------------------------- --------------------------------------
-53524955535055524769525243,5249435247 -53524955535055524769525243,5249435248
SDIFF
------------
0,0000000001
