将 Oracle DATE 转换为 Unix 风格的时间(自 1906 年以来的秒数?)
我需要将 Oracle DATE 值转换为 Unix 风格的 seconds-since-epoch-start 值.
I need to convert an Oracle DATE value to a Unix style seconds-since-epoch-start value.
我尝试了各种 Oracle 转换组合，例如:
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 日以来的秒数，则:
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 中减去两个日期时，结果是 天数.然后你必须将它乘以 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)，但是 - 前者难以理解，而后者显示发生了什么以及为什么.
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'.
相关文章