SQLite与Oracle - 计算日期差异 - 小时 [英] SQLite vs. Oracle - Calculating date differences - hours

问题描述

我想知道有没有人看到这个，如果有解决方案，或者我只是没有做正确的事情。我正在尝试在数据库记录中的现在和创建日期之间的时间差异 - 不是想要得到总时数，而是在你消除总天数后剩下的时间，所以你可以输出一些东西是x天，x小时。

I wonder if anyone has seen this, and if there is a solution, or if I'm just not doing something right. I'm trying to the hours difference between now and a "created date" in a database record - not trying to get the total hours, but the hours left after you get rid of total days, so you can output that something is x days, x hours old.

初始Givens

让我们使用 SYSDATE 或now， 12/6/2016 6:41 PM 。

Let's use a SYSDATE, or "now", of 12/6/2016 6:41 PM.

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

Let's say I have an Oracle table and a SQLite table that we will call MyTable. In it, I have a CREATED_DATE field with dates stored in local time:

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

两个表都是相同的，除了它的类型为$ code> DATE ，但在SQLite中，您必须将日期存储为格式为yyyy-MM-dd HH：mm：ss的字符串。但是每个表的值都是一样的。

Both tables are identical, except that it is of type DATE in Oracle, but in SQLite, you have to store a date as a string with a format 'yyyy-MM-dd HH:mm:ss'. But each table's values are the same.

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

I start off getting the total days difference between "now" and the dates. I can subtract out the integer days from the decimal days and get the hours I need.

总天数 - Oracle

如果我在Oracle中执行此操作，请给我总差额：

SELECT（SYSDATE - CREATED_DATE）FROM MyTable

If I do this in Oracle, to give me the total days difference:
SELECT (SYSDATE - CREATED_DATE) FROM MyTable

我得到 686.211284 ... 为第一个和 180.144976 ..

I get 686.211284... for the first one and 180.144976... for the second.

总天数 - SQLite

如果我这样做使用SQLite来给我总的天差，第一个是相当接近，但第二个是真的关闭：

SELECT（julianday（'now '） - julianday（CREATED_DATE，'utc'））FROM MyTable

If I do this using SQLite to give me the total days difference, the first one is pretty close, but the second is really off:
SELECT (julianday('now') - julianday(CREATED_DATE, 'utc')) FROM MyTable

我得到 686.212924 .... 为第一个，$ code> 180.188283 ... 为第二个。

I get 686.212924.... for the first one and 180.188283... for the second.

问题

我添加了'utc' / code>在SQLite查询，因​​为我知道 julianday（）使用GMT。否则时间约6小时。问题是他们现在已经1小时了，但并不是所有的时间。第一个结果给出正确的小时数差异：5，在这两种情况下：

I added 'utc' on the SQLite query because I know that julianday() uses GMT. Otherwise the hours were about 6 hours off. The problem is they are now 1 hour off, but not all of the time. The first result gives the right number of hours difference: 5, in both cases:

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

当我翻译这些值时，它给了我结果

When I floor those values, it gives me the result I need.

尽管如此，这里是我所得到的：

With that second one, though, here is what I get:

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

巨大的差异 - 一个小时不同！任何人都可以帮忙解决这个问题，如果有办法解决这个问题/确保准确吗？

A HUGE difference - a whole hour different! Can anyone help with why this is, and if there's a way to fix it/make it accurate?

获取小时

这是我用来获取时间的代码。当我使用Oracle时，我确认了使用计算器进行仔细检查的时间。为此，我使用：

This is the code I'm using to get the hours. I have confirmed the hours I get when I use Oracle are correct by using a calculator to double-check it. For that, I use:

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

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

I am currently trying to get the hours in SQLite using a similar setup:

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

我已经停止了SQLite结果的地板或整数转换，目前是有目的的。两个查询基本上将总天数减去整数总天数，以获得十进制余数（这是代表小时数的一天的部分），并将其乘以24.

I've left off the "flooring", or integer casting, of the SQLite result, for now, on purpose. Both queries basically take the total days minus the integer total days to get the decimal remainder (which is the portion of a day that represents hours) and multiplies it by 24.

这是有趣的，但是，因为我使用上面的整个小时减去一个casted版本的整数小时的相同的查询，留下小数部分的分钟，并乘以60，它出来完美的分钟。

It is funny, though, because I am using the same query above for the whole hours minus a casted version of it for the integer hours, leaving the decimal portion for minutes, and multiplying that by 60 and it comes out perfectly for the minutes.

屏幕截图：并排比较

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

This was taken at 12/6/2016 7:20 PM, with SQLite shown in my application on the left, Oracle query being done in Oracle SQL Developer on the right:

推荐答案

其实你错过了一个重要的信息你认为哪个价值？正确吗你必须考虑夏令时吗？

Actually you missed one important information: Which value do you consider as being correct? Do you have to consider Daylight-Saving hours or not?

从 Oracle 开始：

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

I assume data type of column CREATED_DATE is DATE. SYSDATE returns also a DATE value. DATE value does not have any time zone (i.e. Daylight-Saving time settings) information.

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

Assuming now is 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 ），请看这个例子：

If you have to consider Daylight-Saving time you have to use data type TIMESTAMP WITH TIME ZONE (or TIMESTAMP WITH LOCAL TIME ZONE), see this example:

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小时。

Result is +180 01:00:00.000000, i.e. 180 days and 1 hour.

这取决于您需要使用哪一项。一般来说，我建议使用 TIMESTAMP TIMESTAMP WITH TIME ZONE 而不是 DATE ，因为您可以使用 EXTRACT（datetime）获取时间，您不必用 FLOOR 和这样的东西：

It depends on your requirement which one you have to use. In general I would recommend to use TIMESTAMP, resp. TIMESTAMP WITH TIME ZONE rather than DATE, because there you can simply use EXTRACT(datetime) to get the hours and you don't have to fiddle with FLOOR and such stuff:

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

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

Note, LOCALTIMESTAMP returns a TIMESTAMP value, use SYSTIMESTAMP, resp. CURRENT_TIMESTAMP to get current time as TIMESTAMP WITH TIME ZONE value.

现在考虑 SQLite ：

更新

实际 julianday（'now'） - julianday（CREATED_DATE，'utc'）给出正确的结果 - 或者让我们称之为精确结果 。考虑到夏令时的转变。例如2016-10-31 00:00:00 - 2016-10-30 00:00:00（欧洲时间）的差异是25小时 - 不是24小时！

Actually julianday('now') - julianday(CREATED_DATE, 'utc') gives correct result - or let's call it "precise result". It takes Daylight-Saving shifts into account. For example difference of '2016-10-31 00:00:00' - '2016-10-30 00:00:00' (European times) is 25 hours - not 24 hours!

现在，您不想忽略计算中的夏令时班。对于Oracle，这很简单，使用 DATE 或 TIMESTAMP 数据类型而不是 TIMESTAMP WITH TIME ZONE ，那么你完成了。

Now, you like to ignore Daylight-Saving shifts in your calculation. For Oracle this was simple, use DATE or TIMESTAMP data types instead of TIMESTAMP WITH TIME ZONE, then you are done.

SQLite总是考虑时区和夏令时的转换，你必须做一些黑客来绕过它。
我有一些时间做了几个测试，我发现了几种方法来做到这一点。

SQLite always considers time zones and Daylight-Saving shifts, you have to make some hacks to bypass it. I got some time to make a few tests and I found several ways to do it.

以下方法都在我的机器上运行（瑞士时间采用夏令时设置，+01：00或+02：00）。

Following methods are all working on my machine (Swiss time with Daylight-Saving settings, +01:00 resp. +02:00).

• julianday（'now'，'localtime'） - julianday（CREATED_DATE）


• julianday（datetime（'now'，'localtime'）||'Z'） - julianday（CREATED_DATE ||'Z'）

• 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天））只是为了格式化目的，它不适合超过1年的三角洲。

I used strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', ..., '-1 day')) just for formatting purpose, it is not suitable for deltas which span over more than 1 year.

这篇关于SQLite与Oracle - 计算日期差异 - 小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！