問題描述
我需要將 Oracle DATE 值轉(zhuǎn)換為 Unix 風格的 seconds-since-epoch-start 值.
I need to convert an Oracle DATE value to a Unix style seconds-since-epoch-start value.
我嘗試了各種 Oracle 轉(zhuǎn)換組合，例如:
I've tried various combinations of Oracle's conversions such as:
select to_number(to_date('10/05/2019','mm/dd/yyyy')) from dual;
select to_number(to_timestamp(to_date('10/05/2019','mm/dd/yyyy'))) from dual;
select to_number(to_char(to_date('10/05/2019','mm/dd/yyyy'))) from dual;
似乎沒有任何效果.有人對此有答案嗎?
Nothing seems to work. Does anyone have an answer to this?
推薦答案
如果這是自 1906 年 1 月 1 日以來的秒數(shù)，則:
If that's number of seconds since Jan 01 1906, then:
SQL> select sysdate - date '1906-01-01' days,
2 (sysdate - date '1906-01-01') * 24 * 60 * 60 unix_style
3 from dual;
DAYS UNIX_STYLE
---------- ----------
41555,811 3590422068
SQL>
為什么?因為 - 當您在 Oracle 中減去兩個日期時，結(jié)果是 天數(shù).然后你必須將它乘以 24(一天有 24 小時)，乘以 60(一小時有 60 分鐘)，再乘以 60(一分鐘有 60 秒).
Why? Because - when you subtract two dates in Oracle, result is number of days. Then you have to multiply it by 24 (as there are 24 hours in a day), by 60 (as there are 60 minutes in an hour) and once again by 60 (as there are 60 seconds in a minute).
當然，您可以將其乘以 86400(即 24 * 60 * 60)，但是 - 前者難以理解，而后者顯示發(fā)生了什么以及為什么.
Of course, you could have multiplied it by 86400 (which is 24 * 60 * 60), but - former is difficult to understand while latter shows what's going on and why.
如果 - 正如 Wernfried 評論的那樣 - 日期與您所說的不同，您只需將 date '1906-01-01' 替換為 date '1970-01-01'.
If - as Wernfried commented - date differs from the one you said, you'd just replace date '1906-01-01' with date '1970-01-01'.
這篇關(guān)于將 Oracle DATE 轉(zhuǎn)換為 Unix 風格的時間(自 1906 年以來的秒數(shù)?)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！