将时间戳数据类型转换为 unix 时间戳 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12105691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert timestamp datatype into unix timestamp Oracle
提问by deepti
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?
我在数据库中有一个时间戳数据类型，格式为 24-JuL-11 10.45.00.000000000 AM，想将其转换为 unix 时间戳，我怎样才能得到它？
回答by Ben
This question is pretty much the inverse of Convert Unixtime to Datetime SQL (Oracle)
这个问题几乎是Convert Unixtime to Datetime SQL (Oracle) 的反面
As Justin Cave says:
正如贾斯汀凯夫所说：
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
没有内置函数。但是写一个相对容易。由于 Unix 时间戳是自 1970 年 1 月 1 日以来的秒数
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;
As its in secondssince 1970 the number of fractional seconds is immaterial. You can still call it with a timestamp data-type though...
由于自 1970 年以来以秒为单位，因此小数秒的数量无关紧要。不过，您仍然可以使用时间戳数据类型调用它...
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>
There's 3,600 seconds difference, i.e. 1 hour.
有 3,600 秒的差异，即 1 小时。
回答by James Sumners
I realize an answer has already been accepted, but I think it should be made clear that the function in that answer doesn't consider the passed in date's time zone offset. A proper Unix timestamp should be calculated at GMT (+0). Oracle's to_datefunction assumes the passed in date is in the local time zone unless otherwise specified. This problem is exacerbated by the fact that Daylight Saving Time is a real thing. I over came this problem with the following function:
我意识到已经接受了一个答案，但我认为应该明确指出该答案中的函数不考虑传入的日期时区偏移量。应在 GMT (+0) 计算正确的 Unix 时间戳。to_date除非另有说明，否则Oracle 的函数假定传入的日期在本地时区。由于夏令时是真实存在的，这个问题更加严重。我用以下功能解决了这个问题：
create or replace
function unix_time_from_date
(
in_date in date,
in_src_tz in varchar2 default 'America/New_York'
)
return integer
as
ut integer := 0;
tz varchar2(8) := '';
tz_date timestamp with time zone;
tz_stmt varchar2(255);
begin
/**
* This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC).
*
* @author James Sumners
* @date 01 February 2012
*
* @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone.
* @param in_src_tz Indicates the time zone of the in_date parameter.
*
* @return integer
*/
-- Get the current timezone abbreviation (stupid DST)
tz_stmt := 'select systimestamp at time zone ''' || in_src_tz || ''' from dual';
execute immediate tz_stmt into tz_date;
select
extract(timezone_abbr from tz_date)
into tz
from dual;
-- Get the Unix timestamp
select
(new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400)
into ut
from dual;
return ut;
end unix_time_from_date;
I have some companion functions, unix_timeand unix_time_to_date, available at http://jrfom.com/2012/02/10/oracle-and-unix-timestamps-revisited/. I can't believe Oracle has made it all the way to 11g without implementing these.
我有一些配套函数，unix_time并且unix_time_to_date可以从http://jrfom.com/2012/02/10/oracle-and-unix-timestamps-revisited/ 获得。我不敢相信甲骨文在没有实现这些的情况下就一路走到了 11g。
回答by Andre Kirpitch
for date:
日期：
FUNCTION date_to_unix (p_date date,in_src_tz in varchar2 default 'Europe/Kiev') return number is
begin
return round((cast((FROM_TZ(CAST(p_date as timestamp), in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
end;
for timestamp:
对于时间戳：
FUNCTION timestamp_to_unix (p_time timestamp,in_src_tz in varchar2 default 'Europe/Kiev') return number is
begin
return round((cast((FROM_TZ(p_time, in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
end;
回答by dpedro
I'm using following method, which differs a little from other answers in that it uses sessiontimezone()function to properly get date
我正在使用以下方法，它与其他答案略有不同，因为它使用sessiontimezone()函数来正确获取日期
select
(
cast((FROM_TZ(CAST(in_date as timestamp), sessiontimezone) at time zone 'GMT') as date) -- in_date cast do GMT
-
TO_DATE('01.01.1970','dd.mm.yyyy') -- minus unix start date
)
* 86400000 -- times miliseconds in day
from dual;
回答by Piotr R
SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL
回答by Wernfried Domscheit
For conversion between Oracle time and Unix times I use these functions.
They consider your current timezone. You should also add DETERMINISTICkeyword, for example if you like to use such function in a function-based index. Conversion between DATEand TIMESTAMPshould be done implicitly by Oracle.
为了在 Oracle 时间和 Unix 时间之间进行转换，我使用了这些函数。他们会考虑您当前的时区。您还应该添加DETERMINISTIC关键字，例如，如果您喜欢在基于函数的索引中使用此类函数。DATE和之间的转换TIMESTAMP应该由 Oracle 隐式完成。
FUNCTION Timestamp2UnixTime(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS
timestampUTC TIMESTAMP;
theInterval INTERVAL DAY(9) TO SECOND;
epoche NUMBER;
BEGIN
timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC';
theInterval := TO_DSINTERVAL(timestampUTC - TIMESTAMP '1970-01-01 00:00:00');
epoche := EXTRACT(DAY FROM theInterval)*24*60*60
+ EXTRACT(HOUR FROM theInterval)*60*60
+ EXTRACT(MINUTE FROM theInterval)*60
+ EXTRACT(SECOND FROM theInterval);
RETURN ROUND(epoche);
END Timestamp2UnixTime;
FUNCTION UnixTime2Timestamp(UnixTime IN NUMBER) RETURN TIMESTAMP DETERMINISTIC IS
BEGIN
RETURN (TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixTime * INTERVAL '1' SECOND) AT LOCAL;
END UnixTime2Timestamp;
回答by rogerdpack
This was what I came up with:
这是我想出的：
select substr(extract(day from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 24 * 60 * 60 +
extract(hour from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 * 60 +
extract(minute from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 +
trunc(extract(second from (n.origstamp - timestamp '1970-01-01 00:00:00')),0),0,15) TimeStamp
from tablename;
FWIW
FWIW
回答by user5193849
SELECT
to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') dt,
round((sysdate - to_date('19700101 000000', 'YYYYMMDD HH24MISS'))*86400) as udt
FROM dual;
