将时间戳数据类型转换为UNIX时间戳Oracle [英] Convert timestamp datatype into unix timestamp 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?
推荐答案
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屋!