SQLite vs. Oracle - Calculating date differences -

Question

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 get 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.

Initial Givens

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

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

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.

Total Days - Oracle

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

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

Total Days - SQLite

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

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

Problem

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?

Getting the hours

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

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)

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.

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.

Screenshot: Side by Side Comparison

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:

Answer 1

As far as I get your problem, it seem the daylight change was lost for SQLite, or to be exact, that you need to specify this change on the save (as it's a string and not a true date field / transparent-timestamp).

When you generate (? if you do) the date, make it full UTC with time zone explicit and not local implicit :

SQLite Date / Time

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time strings are equivalent:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00

To be honest, the SQLite with utc is not wrong when it come to "forget" the daylight change, as UTC don't move (it's chronometric physicals hours). If you told him everything is at the same UTC time-zone, it'll just make a simple subtract and don't give a ficus about your daylight.

Don't your julianday('now') take a 'UTC' too ? (sorry if I don't get it, I'll take a second look tomorrow)

Answer 2

I know this isn't at all the ideal way to do this, but I solved this myself using a compensator function in my C# application that was pulling the SQLite values. It actually occurred to me after I wrote this that I could've just re-done the date subtraction in C# and over-wrote my Age field! But since I just needed to modify the hours (and days, if hours was 0 and it was a DST date) for the given condition, I just used this.

So I have a class that will provide a DataTable of results based on the query I provide to a public static string variable in that class and another function I call. Then, I call BindTable() to bind that table to the ListView in my WPF app to display the information.

I pull my DataTable in by calling ADOClass.adoDataTable. Once I have the DataTable, I just iterate through the rows, store the Created Date as a variable, and the Age string (which I use the function to update as necessary) as a variable. If the Created Date meets the .IsDaylightSavingTime() condition, it has to get an hour subtracted. If the Age was say, 0 hours (and some odd minutes) old, we have to set the days back one day, and the hours to 23.

private void BindTable()
{            
    DataTable dt = ADOClass.adoDataTable;

    if (!Oracle_DAL.ConnTest()) // if no Oracle connection, SQLite is running and it needs DST correction
    {
        int oldHours = 0;
        int newHours = 0;
        int oldDays = 0;
        int newDays = 0;
        string ageCell = String.Empty;
        string hoursString = String.Empty;
        string daysString = String.Empty;
        string crDate = String.Empty;

        if (dt != null && dt.Rows != null)
        {
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    crDate = dr["CREATED_DATE"] != null ? dr["CREATED_DATE"].ToString() : String.Empty;
                    if (!String.IsNullOrEmpty(crDate))
                    {
                        DateTime createdDate = DateTime.Parse(crDate);
                        if (createdDate.IsDaylightSavingTime())
                        {
                            ageCell = dr["AGE"] != null ? dr["AGE"].ToString() : String.Empty;
                            if (!String.IsNullOrEmpty(ageCell))
                            {
                                hoursString = ageCell.Split(',')[3];
                                hoursString = hoursString.TrimStart(' ');
                                oldHours = int.Parse(hoursString.Split(' ')[0]);

                                if (oldHours == 0)
                                {
                                    newHours = 23;
                                    daysString = ageCell.Split(',')[2];
                                    daysString = daysString.TrimStart(' ');
                                    oldDays = int.Parse(daysString.Split(' ')[0]);
                                    oldDays--;
                                    newDays = oldDays;
                                    ageCell = ageCell.Replace(daysString, newDays.ToString() + " days");
                                    dr["AGE"] = ageCell;
                                }
                                else
                                {
                                    oldHours--;
                                    newHours = oldHours;                                    
                                }
                                dr["AGE"] = ageCell.Replace(hoursString, newHours.ToString() + " hours");
                            }
                        }
                    }
                }                        
            }
        }
    }

    lstData.DataContext = dt;  // binds to my ListView's grid
}

(NOTE: I realized later that, because I tested this function on a non-DST day, and this was meant to fix issues with DST dates, that when it becomes DST again, I believe things will change. I think I'd end up having to add an hour to my results for non-DST dates to make it work, at that point. So this would have to have an additional check if DateTime.Now conforms to .IsDaylightSavingTime() in order to know what to do. I may re-visit this post at that time.)

And in case anyone is curious..... Here is the full query I was running in Oracle:

SELECT CREATED_DATE, (SYSDATE - CREATED_DATE) AS TOTALDAYS, 
FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12) || ' years, '  
|| (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) - 
   (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12)) * 12) || ' months, '  
-- we take total days - years(as days) - months(as days) to get remaining days
|| FLOOR((SYSDATE - CREATED_DATE) -      -- total days
   (FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) -      -- years, as days
   -- this is total months - years (as months), to get number of months, 
   -- then multiplied by 30.416667 to get months as days (and remove it from total days)
   FLOOR(FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12)))  
|| ' days, '   
-- Here, we can just get the remainder decimal from total days minus 
-- floored total days and multiply by 24       
|| FLOOR(
     ((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
   )
|| ' hours, ' 
-- Minutes just use the unfloored hours equation minus floored hours, 
-- then multiply by 60
|| ROUND(
       (
         (
           ((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
         ) - 
         FLOOR((((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24))
       )*60
    )
|| ' minutes'  
AS AGE FROM MyTable`

And this is my final, full query for SQLite from my application:

    private static readonly string mainqueryCommandTextSQLite = "SELECT " + 
        "CREATED_DATE, " +
        " (julianday('now') - julianday(CREATED_DATE, 'utc')) AS TOTALDAYS, " +
        //            " (((julianday('now') - julianday(CREATED_DATE))/365)*12) || ' total months, ' || " +
        //            " ((CAST ((julianday('now') - julianday(CREATED_DATE))/365 AS INTEGER))*12) || ' years as months, ' || " +

        // Provide years, months
        " CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER) || ' years, ' || " +
        " CAST (((((julianday('now') - julianday(CREATED_DATE, 'utc'))/365)*12) - (CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER)*12)) AS INTEGER)  || ' months, ' " +

        // Provide days
        "|| ((CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER) - " +  // total number of days
        " (CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER)*365) ) -" + // years in days  
        " CAST((30.41667 * ((CAST ((((julianday('now') - julianday(CREATED_DATE, 'utc'))/365)*12) AS INTEGER)) - ((CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) / 365 AS INTEGER)) * 12))) AS INTEGER)) " + // days of remaining months using total months - months from # of floored years * (365/12)
        " || ' days, ' " +

        // BUG:  These next two do not get accurate hours during DST months (March - Nov)
        // This gives hours
        "|| CAST ((((julianday('now') - julianday(CREATED_DATE, 'utc')) - " +
        " CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) AS INTEGER) " +

        // This gives hours.minutes
        //"|| (((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) " +

        // This gives days.hours, but taking the decimal and multiplying by 24 to get actual hours 
        // gives an incorrect result
        //"|| ((" +
        //        "(0.0 + strftime('%S', 'now', 'localtime') " +
        //        "+ 60*strftime('%M', 'now', 'localtime') " +
        //        "+ 24*60*strftime('%H', 'now', 'localtime') " +
        //        "+ 24*60*60*strftime('%j', 'now', 'localtime')) - " +
        //        "(strftime('%S', CREATED_DATE) " +
        //        "+ 60*strftime('%M', CREATED_DATE) " +
        //        "+ 24*60*strftime('%H', CREATED_DATE) " +
        //        "+ 24*60*60*strftime('%j', CREATED_DATE)) " +
        //    ")/60/60/24) " +
        "|| ' hours, ' " +

        // Provide minutes
        "|| CAST (ROUND(((((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) - " +
        "(CAST((((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER)) * 24) AS INTEGER)))*60) AS INTEGER)" +
        "|| ' minutes' " +
        " AS AGE FROM MyTable";

And new screenshot, showing everything matching (except total days, which I can alter by just subtracting 1/24 from in my C# function and update the same way, for the DST dates):

UPDATE

Since Wernfried found 2 queries in SQLite that negate the need for this function, I'm going to accept that answer for how to really solve this issue:

For Oracle -

• SELECT (SYSDATE - CREATED_DATE) FROM MyTable

or using to_date format syntax is good for getting the days.hours, and doing conversions. Taking the decimal portion and multiplying by 24 is good for hours and is independent of DST, like I wanted. See above for full query I used for formatting into years, months, days, hours, and minutes.

For SQLite -

As Wernfried found, either of these will work:

• julianday('now', 'localtime') - julianday(CREATED_DATE)

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

And this avoids the need for my function above.

If you use:

• julianday('now') - julianday(CREATED_DATE, 'utc')

like I have in the earlier code, above, then you would need my DST compensator function, farther above.

Answer 3

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

Start with Oracle:

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.

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;

returns exactly 180 Days.

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;

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

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;

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

Now consider SQLite:

Update

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!

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 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.

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')

See test cases:

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

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.