如何从日期 01-01-9999 的 oracle 的日期字段中获取以毫秒为单位的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36323694/
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
how to get time in millisecond from date field of oracle for the date 01-01-9999
提问by user3812269
I want to get milliseconds from date field of oracle for date "01-01-9999".
我想从 oracle 的日期字段中获取日期“01-01-9999”的毫秒数。
I have created below block to achieve the same.
我创建了下面的块来实现相同的目的。
set serveroutput on;
declare
base_point constant timestamp := to_timestamp_tz('01-JAN-1970 00:00:00.000+00:00', 'DD-Mon-RR HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC';
now timestamp := to_timestamp_tz('01-01-2099 00:00:00.000+00:00', 'DD-MM-RR HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC';
-- now constant timestamp := systimestamp AT TIME ZONE 'UTC' ;
n number;
begin
select to_timestamp_tz(to_char(todate,'DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM')
into now
from t_table where ACCOUNTID = 'ACC001124211';
DBMS_OUTPUT.put_line(' now :'||now);
n := (
((extract(day from (now-base_point)))*86400)
+ ((extract(hour from (now-base_point)))*3600)
+ ((extract(minute from (now-base_point)))*60)
+ ((extract(second from (now-base_point))))
) * 1000;
DBMS_OUTPUT.put_line(' n :'||n);
end;
/
but using above block I am getting value as 4070908800000, which is equal to date 1/1/2099but actual date in my table is 01-01-9999
但是使用上面的块我得到的价值为4070908800000，它等于日期 1/1/2099但我表中的实际日期是01-01-9999
Can you please help us to get exact millisecond using date field
你能帮助我们使用日期字段获得精确的毫秒吗
回答by Lalit Kumar B
No need of PL/SQL, you could do it in plain SQL.
不需要PL/SQL，你可以用普通的SQL 来完成。
To convert a date to millisecondssince 01-JAN-1970:
要将日期转换为自 以来的毫秒数01-JAN-1970：
SQL> SELECT to_number(DATE '9999-01-01'
2 - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000) milliseconds
3 FROM dual;
MILLISECONDS
------------------
253370764800000
SQL>
回答by Noel
The reason why you are getting wrong value is this statement.
你得到错误价值的原因是这个陈述。
select to_timestamp_tz(to_char(todate,'DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM')
Since your format element for year is YY, to_char conversion will have only 2 digits for year.
由于您的年份格式元素是 YY，因此 to_char 转换将只有 2 位数的年份。
select to_char(date'9999-01-01','DD-MM-YY HH24:MI:SS')||'.000+00:00' char_date
from dual
char_date
------------
01-01-99 00:00:00.000+00:00
When you convert this to timestamp using YY as format element, the year returned always has the same first 2 digits as the current year, which is why you get 2099 as year.
当您使用 YY 作为格式元素将其转换为时间戳时，返回的年份始终具有与当前年份相同的前 2 位数字，这就是为什么您将 2099 作为年份。
select to_char(to_timestamp_tz(to_char(date'9999-01-01','DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM'),'yyyy') char_date
from dual;
char_date
------------
2099
Moral of the story:
故事的道德启示：
Oracle recommends that you use the 4-digit year element (YYYY) instead of the shorter year elements for these reasons:
由于以下原因，Oracle 建议您使用 4 位年份元素 (YYYY) 而不是较短的年份元素：
- The 4-digit year element eliminates ambiguity.
- The shorter year elements may affect query optimization because the year is not known at query compile time and can only be determined at run time.
- 4 位年份元素消除了歧义。
- 较短的年份元素可能会影响查询优化，因为年份在查询编译时是未知的，只能在运行时确定。
