将 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'.