oracle - SQLite与Oracle-计算日期差异-小时

Question

我想知道是否有人看到过这种情况，是否有解决方案，或者我只是做错了什么。我试图获取现在和数据库记录中“创建日期”之间的小时数差异，而不是试图获取总小时数，而是去除总天数后剩下的小时数，因此您可以输出x天，x个小时。

初始纪要

让我们使用SYSDATE的12/6/2016 6:41 PM或“现在”。

假设我有一个Oracle表和一个SQLite表，我们将其称为MyTable。在其中，我有一个CREATED_DATE字段，其中的日期存储在本地时间：

CREATED_DATE
------------
1/20/2015 1:35:17 PM
6/9/2016 3:10:46 PM

除了在Oracle中类型为 DATE之外，两个表都是相同的，但是在SQLite中，必须将日期存储为字符串，格式为'yyyy-MM-dd HH：mm：ss'。但是每个表的值都相同。

我开始获取“现在”和日期之间的总天数差异。我可以从十进制天中减去整数天，并得到所需的小时数。

总天数-Oracle

如果我在Oracle中执行此操作，请给我总天数差异：
SELECT (SYSDATE - CREATED_DATE) FROM MyTable

我第一个得到 686.211284...，第二个得到 180.144976...。

总天数-SQLite

如果我使用SQLite来计算总天数差异，那么第一个就非常接近了，但是第二个真的就关闭了：
SELECT (julianday('now') - julianday(CREATED_DATE, 'utc')) FROM MyTable

我第一个得到 686.212924....，第二个得到 180.188283...。

问题

我在SQLite查询上添加了 'utc'，因为我知道 julianday()使用GMT。否则，工作时间将减少约6个小时。问题是他们现在有1个小时的假，但不是所有时间都有。第一个结果给出正确的小时数差异：5，在两种情况下：

.211284 x 24 = 5.07 hours
.212924 x 24 = 5.11 hours

当我设定这些值时，它将为我提供所需的结果。

但是，有了第二个，我得到的是：

.144976 x 24 = 3.479 hours
.188283 x 24 = 4.519 hours

巨大的差异-整整一个小时！谁能帮忙解决这个问题，是否有办法解决/使其准确？

节省时间

这是我用来获取时间的代码。通过使用计算器进行仔细检查，我已经确认使用Oracle的时间正确无误。为此，我使用：

SELECT FLOOR(((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24) FROM MyTable

我目前正在尝试使用类似的设置来获取SQLite的时间：

(((julianday('now') - julianday(CREATED_DATE, 'utc')) - 
CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24)

现在，我故意放弃了SQLite结果的“填充”或整数转换。这两个查询基本上都用总天数减去整数总天数来得到小数点后的余数（这是一天中代表小时的部分）并将其乘以24。

不过，这很有趣，因为我在整个小时中都使用了上面的相同查询，然后在整数小时中减去了它的强制转换版本，将小数部分保留了几分钟，然后乘以60，就可以得出完美的分钟数。

屏幕截图：并排比较

这是在 12/6/2016 7:20 PM上进行的，SQLite显示在我的应用程序的左侧，Oracle查询在Oracle SQL Developer中的右侧进行：

Answer 1

实际上，您错过了一个重要信息：您认为哪个值正确？您是否需要考虑节省日光的时间？

从Oracle开始：

我假设列CREATED_DATE的数据类型是DATE。 SYSDATE还返回一个DATE值。 DATE值没有任何时区（即夏时制设置）信息。

假设现在是2016-12-06 06:00:00：

SELECT 
   TO_DATE('2016-12-06 06:00:00','YYYY-MM-DD HH24:MI:SS') 
   - TO_DATE('2016-06-09 06:00:00','YYYY-MM-DD HH24:MI:SS') 
FROM dual;

精确返回180天。

如果必须考虑夏令时，则必须使用数据类型 TIMESTAMP WITH TIME ZONE（或 TIMESTAMP WITH LOCAL TIME ZONE），请参见以下示例：

SELECT 
   TO_TIMESTAMP_TZ('2016-12-06 06:00:00 Europe/Zurich','YYYY-MM-DD HH24:MI:SS TZR') 
   - TO_TIMESTAMP_TZ('2016-06-09 06:00:00 Europe/Zurich','YYYY-MM-DD HH24:MI:SS TZR') 
FROM dual;

结果为 +180 01:00:00.000000，即180天零1小时。

这取决于您的要求，您必须使用哪一个。通常，我建议分别使用 TIMESTAMP。 TIMESTAMP WITH TIME ZONE而不是 DATE，因为您可以在其中简单地使用 EXTRACT(datetime)来获取小时数，而不必摆弄 FLOOR之类的东西：

 SELECT 
    EXTRACT(HOUR FROM SYSTIMESTAMP - CREATED_DATE) AS diff_hours 
FROM MyTable;

注意， LOCALTIMESTAMP返回一个 TIMESTAMP值，请分别使用 SYSTIMESTAMP。 CURRENT_TIMESTAMP获取当前时间作为 TIMESTAMP WITH TIME ZONE值。

现在考虑SQLite：

更新资料

实际上 julianday('now') - julianday(CREATED_DATE, 'utc')给出正确的结果-或称其为“精确结果”。它需要考虑日光节约。例如，“ 2016-10-31 00:00:00”-“ 2016-10-30 00:00:00”（欧洲时间）的时差为25小时，而不是24小时！

现在，您想在计算中忽略夏时制转换。对于Oracle，这很简单，使用 DATE或 TIMESTAMP数据类型而不是 TIMESTAMP WITH TIME ZONE，就可以了。

SQLite总是考虑时区和夏时制，您必须进行一些修改才能绕过它。
我有时间进行一些测试，并且找到了几种方法来进行测试。

以下方法都可以在我的机器上使用（瑞士时间与夏时制设置，+ 01：00或+02：00）。


julianday('now', 'localtime') - julianday(CREATED_DATE)
julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')


查看测试用例：

create table t (CREATED_DATE DATE);

insert into t values (datetime('2015-06-01 00:00:00'));
insert into t values (datetime('2015-12-01 00:00:00'));
insert into t values (datetime('2016-06-01 00:00:00'));
insert into t values (datetime('2016-12-01 00:00:00'));

select datetime('now', 'localtime') as now, 
    created_date, 
    julianday('now') - julianday(CREATED_DATE, 'utc') as wrong_delta_days,
    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday('now') - julianday(CREATED_DATE, 'utc'))||' day', '-1 day')) as wrong_delta,    

    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday('now', 'localtime') - julianday(CREATED_DATE))||' day', '-1 day')) as delta_1, 
    strftime('%j %H:%M:%S',
       datetime('now', 'localtime', 
          '-'||strftime('%Y', CREATED_DATE)||' year', 
          '-'||strftime('%j', CREATED_DATE)||' day', 
          '-'||strftime('%H', CREATED_DATE)||' hour', 
          '-'||strftime('%M', CREATED_DATE)||' minute', 
          '-'||strftime('%S', CREATED_DATE)||' second'
         )) as delta_2,
    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z'))||' day', '-1 day')) as delta_3
from t;


now                 | CREATED_DATE        | wrong_delta_days | wrong_delta  | delta_1      | delta_2      | delta_3
2016-12-08 08:34:08 | 2015-06-01 00:00:00 | 556.398711088113 | 190 09:34:08 | 190 08:34:08 | 190 08:34:08 | 190 08:34:08
2016-12-08 08:34:08 | 2015-12-01 00:00:00 | 373.357044421136 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08
2016-12-08 08:34:08 | 2016-06-01 00:00:00 | 190.398711088113 | 190 09:34:08 | 190 08:34:08 | 190 08:34:08 | 190 08:34:08
2016-12-08 08:34:08 | 2016-12-01 00:00:00 | 7.35704442113638 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08

我使用 strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', ..., '-1 day'))仅用于格式化目的，它不适合跨越1年以上的增量。