database - 考虑夏令时将(日期)类型的oracle历史数据转换为unix时间戳

Question

我能够在 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 小时的差异。

Answer 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 该日期应该代表哪个时区。如果将日期转换为时间戳，它基本上保持不变。如果您将它转换为带有时区的时间戳，那么它会采用服务器的时区。然后，您可以使用 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

但您仍然必须声明 - 因此可能假设 - 日期最初代表的时区。您已经在您的职能中这样做了，所以我认为这不是问题。