我可以在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/10/oracle-and-unix-timestamps-revisited/
由于存储的对象没有时区，它将以sys时区为cdt，并且cst时区的数据相差1小时。
您的功能是提取今天的时区缩写并将其应用于提供的日期，有效地假设所有日期都在该时区中。它将有一半的时间给出正确的结果-但只有传入的值的一半。（冬天将错误的夏季时间；夏天将错误的冬季时间）。如果您使用区域名称而不是缩写，则不会这样做。但是，那么您将无法使用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时区的时间戳，则它将采用服务器的时区。然后，您可以使用将其转换为UTC at time zone，并从中减去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
但是您仍然必须声明-并因此可以假设-日期最初代表的时区。您已经在函数中这样做了，所以我认为这不是问题。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句