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

查看:123
本文介绍了将时间戳数据类型转换为UNIX时间戳Oracle的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的数据库中有一个时间戳数据类型,格式为11年7月24日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?

推荐答案

这个问题与正如贾斯汀·凯夫(Justin Cave)所说:

As Justin Cave says:

没有内置功能.但是写起来比较容易 一.由于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年以来以 seconds 为单位,小数秒的数量无关紧要.您仍然可以使用时间戳数据类型来调用它...

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>

相差3600秒,即1小时.

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

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆