我能够在unix时间戳中转换日期。但是,我面临将数据库中的历史数据转换以纠正UNIX时间戳的问题。由于数据以日期格式保存,因此这些数据没有可用的时区。有什么办法可以确定CDT还是CST中的任何历史日期。
目前,我正在使用其中一种方法来转换日期。
create or replace function unix_time_from_date
(
in_date in date
-- in_src_tz in varchar2 default 'America/Chicago'
)
return integer
as
ut integer := 0;
tz varchar2(8) := '';
tz_date timestamp with time zone;
tz_stmt varchar2(255);
in_src_tz varchar2(255):='America/Chicago';
begin
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;
从:http://jrfom.com/2015archive/2012/02/02/10/oracle-and-unix-timestamps-revisited/
由于存储的对象没有时区,因此将SYS时区为CDT,并且在CST时区中为数据提供1小时的差异。
您的功能正在提取 today 的时区缩写,并将其应用于所提供的日期,实际上假设所有日期都在该区域中。它将给出正确的结果一半的时间 - 但只有一半的价值观。如果您使用了区域名称而不是缩写,则不会那样做。但是,您无法使用new_time()
,它无论如何只能识别几个区域,因此您必须使用at time zone
。
在过去六个月开始的样本日期(要越过DST边界;这是在伦敦运行的,但也将在芝加哥运行,如果您假设芝加哥),您可以看到当前功能给您的东西:
with t (dt) as (
select add_months(trunc(sysdate), -level)
from dual
connect by level <= 6
)
select dt dt,
unix_time_from_date(dt) unix_time_from_date
from t
order by dt;
DT UNIX_TIME_FROM_DATE
------------------- -------------------
2015-12-21 00:00:00 1450674000
2016-01-21 00:00:00 1453352400
2016-02-21 00:00:00 1456030800
2016-03-21 00:00:00 1458536400
2016-04-21 00:00:00 1461214800
2016-05-21 00:00:00 1463806800
您可以告诉Oracle应该代表日期的时区区域。如果您将日期投入到时间戳,则基本保持不变。如果您将其投放到带有Tome区域的时间戳,则假定服务器的时区。然后,您可以使用at time zone
将其转换为UTC,并从中减去1970-01-01以获取时期号:
with t (dt) as (
select add_months(trunc(sysdate), -level)
from dual
connect by level <= 6
)
select dt dt,
cast(dt as timestamp) ts,
cast(dt as timestamp with time zone) tstz,
cast(dt as timestamp with time zone) at time zone 'UTC' as utc,
86400 * (cast(cast(dt as timestamp with time zone) at time zone 'UTC' as date)
- date '1970-01-01') as epoch
from t
order by dt;
DT TS TSTZ UTC EPOCH
------------------- ------------------- --------------------------------- ----------------------- -----------
2015-12-21 00:00:00 2015-12-21 00:00:00 2015-12-21 00:00:00 Europe/London 2015-12-21 00:00:00 UTC 1450656000
2016-01-21 00:00:00 2016-01-21 00:00:00 2016-01-21 00:00:00 Europe/London 2016-01-21 00:00:00 UTC 1453334400
2016-02-21 00:00:00 2016-02-21 00:00:00 2016-02-21 00:00:00 Europe/London 2016-02-21 00:00:00 UTC 1456012800
2016-03-21 00:00:00 2016-03-21 00:00:00 2016-03-21 00:00:00 Europe/London 2016-03-21 00:00:00 UTC 1458518400
2016-04-21 00:00:00 2016-04-21 00:00:00 2016-04-21 00:00:00 Europe/London 2016-04-20 23:00:00 UTC 1461193200
2016-05-21 00:00:00 2016-05-21 00:00:00 2016-05-21 00:00:00 Europe/London 2016-05-20 23:00:00 UTC 1463785200
作为获得UTC等效的替代方法,仍然基于服务器时区,您可以使用sys_extract_utc
():
with t (dt) as (
select add_months(trunc(sysdate), -level)
from dual
connect by level <= 6
)
select dt dt,
cast(dt as timestamp) ts,
cast(dt as timestamp with time zone) tstz,
sys_extract_utc(cast(dt as timestamp)) as utc,
86400 * (cast(sys_extract_utc(cast(dt as timestamp with time zone)) as date)
- date '1970-01-01') as epoch
from t
order by dt;
,或者如果您不想使用服务器时区,而是指定一个,这对此演示有所帮助:
with t (dt) as (
select add_months(trunc(sysdate), -level)
from dual
connect by level <= 6
)
select dt dt,
cast(dt as timestamp) ts,
from_tz(cast(dt as timestamp), 'America/Chicago') tstz,
from_tz(cast(dt as timestamp), 'America/Chicago') at time zone 'UTC' as utc,
86400 * (cast(from_tz(cast(dt as timestamp), 'America/Chicago') at time zone 'UTC' as date)
- date '1970-01-01') as epoch
from t
order by dt;
DT TS TSTZ UTC EPOCH
------------------- ------------------- ----------------------------------- ----------------------- -----------
2015-12-21 00:00:00 2015-12-21 00:00:00 2015-12-21 00:00:00 America/Chicago 2015-12-21 06:00:00 UTC 1450677600
2016-01-21 00:00:00 2016-01-21 00:00:00 2016-01-21 00:00:00 America/Chicago 2016-01-21 06:00:00 UTC 1453356000
2016-02-21 00:00:00 2016-02-21 00:00:00 2016-02-21 00:00:00 America/Chicago 2016-02-21 06:00:00 UTC 1456034400
2016-03-21 00:00:00 2016-03-21 00:00:00 2016-03-21 00:00:00 America/Chicago 2016-03-21 05:00:00 UTC 1458536400
2016-04-21 00:00:00 2016-04-21 00:00:00 2016-04-21 00:00:00 America/Chicago 2016-04-21 05:00:00 UTC 1461214800
2016-05-21 00:00:00 2016-05-21 00:00:00 2016-05-21 00:00:00 America/Chicago 2016-05-21 05:00:00 UTC 1463806800
并比较计算的时期与您的功能:
with t (dt) as (
select add_months(trunc(sysdate), -level)
from dual
connect by level <= 6
)
select dt dt,
unix_time_from_date(dt) unix_time_from_date,
86400 * (cast(from_tz(cast(dt as timestamp), 'America/Chicago') at time zone 'UTC' as date)
- date '1970-01-01') as epoch,
unix_time_from_date(dt) -
( 86400 * (cast(from_tz(cast(dt as timestamp), 'America/Chicago') at time zone 'UTC' as date)
- date '1970-01-01')) as diff
from t
order by dt;
DT UNIX_TIME_FROM_DATE EPOCH DIFF
------------------- ------------------- ----------- ------
2015-12-21 00:00:00 1450674000 1450677600 -3600
2016-01-21 00:00:00 1453352400 1453356000 -3600
2016-02-21 00:00:00 1456030800 1456034400 -3600
2016-03-21 00:00:00 1458536400 1458536400 0
2016-04-21 00:00:00 1461214800 1461214800 0
2016-05-21 00:00:00 1463806800 1463806800 0
但是您仍必须陈述 - 因此,也许假设 - 最初代表的日期。您已经在功能中这样做了,所以我认为这不是问题。
对于它的价值,这是我为此做出的两个功能:
function toUnixEpoch(d in date) return number
is
begin
return ROUND((cast(from_tz(cast(d as timestamp), 'Europe/Stockholm') at time zone 'UTC' as date) - date '1970-01-01') * 86400);
end;
function toDate(unixEpoch in number) return date
is
begin
return cast(from_tz(timestamp '1970-01-01 00:00:00 UTC'+unixEpoch/86400, 'UTC') at time zone 'Europe/Stockholm' as date);
end;
将它们放入软件包(下面的" Packagename")和,将时区区域设置为自己(上面的"欧洲/斯德哥尔摩")。
使用Alex的完美SQL测试:
with t (d) as (
select TRUNC(add_months(trunc(sysdate), -level))+8/24 from dual connect by level <= 6
)
select d as "Date",
cast(d as timestamp) as "Timestamp",
from_tz(cast(d as timestamp), 'Europe/Stockholm') as "Timestamp with time zone",
from_tz(cast(d as timestamp), 'Europe/Stockholm') at time zone 'UTC' as "UTC",
packagename.toUnixEpoch(d) as "Unix Epoch",
packagename.toDate(wfg.toUnixEpoch(d)) as "Back to Date"
from t
order by 1
您可以看到,"日期"one_answers"返回日期"应该是相同的,无论日光节省如何。