将时间戳数据类型转换为 unix 时间戳 Oracle [英] Convert timestamp datatype into unix timestamp Oracle

问题描述

我在数据库中有一个时间戳数据类型，格式为 24-JuL-11 10.45.00.000000000 AM，想将其转换为 unix 时间戳，我该如何获取?

I have a timestamp datatype in database with format 24-JuL-11 10.45.00.000000000 AM and want to get it converted into unix timestamp, how can I get it?

推荐答案

这个问题几乎与 将 Unixtime 转换为日期时间 SQL (Oracle)

正如贾斯汀凯夫所说:

没有内置函数.但是写起来相对容易一.由于 Unix 时间戳是自 1 月 1 日以来的秒数，1970

There are no built-in functions. But it's relatively easy to write one. Since a Unix timestamp is the number of seconds since January 1, 1970

由于从另一个日期中减去一个日期会导致它们之间的天数，您可以执行以下操作:

As subtracting one date from another date results in the number of days between them you can do something like:

create or replace function date_to_unix_ts( PDate in date ) return number is

   l_unix_ts number;

begin

   l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
   return l_unix_ts;

end;

自 1970 年以来以 秒 为单位，因此小数秒的数量无关紧要.您仍然可以使用时间戳数据类型调用它...

As its in seconds since 1970 the number of fractional seconds is immaterial. You can still call it with a timestamp data-type though...

SQL> select date_to_unix_ts(systimestamp) from dual;

DATE_TO_UNIX_TS(SYSTIMESTAMP)
-----------------------------
                   1345801660

<小时>

为了回应您的评论，我很抱歉，但我没有看到这种行为:

---

In response to your comment, I'm sorry but I don't see that behaviour:

SQL> with the_dates as (
  2    select to_date('08-mar-12 01:00:00 am', 'dd-mon-yy hh:mi:ss am') as dt
  3      from dual
  4     union all
  5    select to_date('08-mar-12', 'dd-mon-yy')
  6      from dual )
  7  select date_to_unix_ts(dt)
  8    from the_dates
  9         ;

DATE_TO_UNIX_TS(DT)
-------------------
         1331168400
         1331164800

SQL>

有 3,600 秒的差异，即 1 小时.

There's 3,600 seconds difference, i.e. 1 hour.

这篇关于将时间戳数据类型转换为 unix 时间戳 Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！