147

How do I get the difference in days between 2 dates in SQLite? I have already tried something like this:

SELECT Date('now') - DateCreated FROM Payment

It returns 0 every time.

Agustin Meriles
  • 4,866
  • 3
  • 29
  • 44

12 Answers12

162
 SELECT julianday('now') - julianday(DateCreated) FROM Payment;
Eder
  • 5
  • 2
Fred
  • 4,846
  • 1
  • 23
  • 21
  • 15
    Note that despite what the function name might make one think, this has higher granularity than days. It's a number of days, but can be fractional. – lindes Jun 11 '12 at 11:54
  • 11
    Bear in mind that julianday returns the (fractional) number of 'days' - i.e. 24hour periods, since *noon* UTC on the origin date. That's usually not what you need, unless you happen to live 12 hours west of Greenwich. E.g. if you live in London, this morning is on the same julianday as yesterday afternoon. – JulianSymes Aug 24 '13 at 11:57
  • 3
    It works if your `DateCreated` is in UTC. If, instead, it is local time, you have to convert `julianday('now')` to local time. I couldn't find anywhere that had this information. If you do `julianday('now') - julianday(DateCreated)` like this post suggests with a date stored in local time, your answer will be off by your offset from GMT and will be wrong. While maybe not best practice to store dates in local time, it can still happen in apps where timezones don't matter (except when the tool you are working with forces them on you, like here). – vapcguy Dec 08 '16 at 19:33
  • 4
    With the assumption the DateCreated is in local time, if you do `julianday('now') - julianday(DateCreated, 'utc')`, to make both UTC, it does not work the same way as doing `julianday('now', 'localtime') - julianday(DateCreated)`. The former does not account for DST days and will add an extra hour to created dates in Mar-Nov. The latter actually does account for it. http://stackoverflow.com/questions/41007455/sqlite-vs-oracle-calculating-date-differences-hours – vapcguy Dec 08 '16 at 19:36
  • Might be a dumb question, but is there an equivalent function for the Gregorian calendar? Or are folks here really using the Julian calendar? Or is the function horribly named and actually works for the Gregorian calendar? – 425nesp Jul 20 '23 at 01:58
96

Difference In Days

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) As Integer)

Difference In Hours

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 As Integer)

Difference In Minutes

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 As Integer)

Difference In Seconds

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 * 60 As Integer)
Abdul Saleem
  • 10,098
  • 5
  • 45
  • 45
  • 2
    for sqlite version 3.12.0 the cast expression has the AS TYPE inside the brackets. e.g. "Select Cast (5.6 As Integer);" https://www.sqlite.org/lang_expr.html#castexpr Otherwise really useful examples. – rob Jul 21 '16 at 21:15
  • Thank you @rob. Someone proposed to edit me like that before. But when I tried it in Sqlitebrowser, it wasn't working. Maybe that because of being older version. So I kept it as it is.. – Abdul Saleem Jul 21 '16 at 22:11
  • I don't want to get an edit rejected and hurt my score, but if `SELECT CAST (julianday('2016-08-05') - julianday('2016-08-01') AS INTEGER);` works for you, I will write it up. – Noumenon Sep 05 '16 at 11:04
  • And Difference in Months is `SELECT (CAST (((julianday('now') - julianday('some-past-date'))/(365/12)) AS INTEGER))` -- I had to use this as a substitute for the Oracle `MONTHS_BETWEEN` function – vapcguy Dec 05 '16 at 16:36
  • A heads up with the usage of JulianDay, the `24` in this case does not work for Eastern Standard Time. I used a value of `21` as my base multiplier for successful conversion of EST times. – Marcus Parsons Oct 18 '18 at 14:56
  • However, in my case, the calculation in minutes is not accurate. – Yusril Maulidan Raji Nov 28 '18 at 11:15
36

Both answers provide solutions a bit more complex, as they need to be. Say the payment was created on January 6, 2013. And we want to know the difference between this date and today.

sqlite> SELECT julianday() - julianday('2013-01-06');
34.7978485878557

The difference is 34 days. We can use julianday('now') for better clarity. In other words, we do not need to put date() or datetime() functions as parameters to julianday() function.

Malachi
  • 3,205
  • 4
  • 29
  • 46
Jan Bodnar
  • 10,969
  • 6
  • 68
  • 77
  • I am not sure, but if this command was in code, and the value Jan 6 2013 came from the database, one needs to consider the datatype used. – NoChance Oct 09 '19 at 11:23
  • 1
    @NoChance The only caveat is that if you're using an INTEGER column for date/time (which must be epoch time [as per docs](https://www.sqlite.org/datatype3.html)), then you'd have to use the `unixepoch` modifier, e.g. `julianday(1643024886, 'unixepoch').`. But for all other acceptable date/time column types, `julianday()` (and the other date/time functions) work fine with no modifiers, as long as you're sticking to the documented acceptable date/time formats. A list of valid arguments to the date/time functions is [here, section 2](https://www.sqlite.org/lang_datefunc.html). – Jason C Jan 24 '22 at 23:52
  • 1
    Check it: http://sqlfiddle.com/#!7/3fdfd/1/0 – Jason C Jan 25 '22 at 00:03
25

The SQLite documentation is a great reference and the DateAndTimeFunctions page is a good one to bookmark.

It's also helpful to remember that it's pretty easy to play with queries with the sqlite command line utility:

sqlite> select julianday(datetime('now'));
2454788.09219907
sqlite> select datetime(julianday(datetime('now')));
2008-11-17 14:13:55
Ken Schumacher
  • 58
  • 1
  • 3
  • 7
converter42
  • 7,400
  • 2
  • 29
  • 24
  • 1
    Since this page comes with a google search engine ranking, here the current link: https://www.sqlite.org/lang_datefunc.html – markusN Mar 07 '16 at 23:40
13

This answer is a little long-winded, and the documentation will not tell you this (because they assume you are storing your dates as UTC dates in the database), but the answer to this question depends largely on the timezone that your dates are stored in. You also don't use Date('now'), but use the julianday() function, to calculate both dates back against a common date, then subtract the difference of those results from each other.

If your dates are stored in UTC:

SELECT julianday('now') - julianday(DateCreated) FROM Payment;

This is what the top-ranked answer has, and is also in the documentation. It is only part of the picture, and a very simplistic answer, if you ask me.

If your dates are stored in local time, using the above code will make your answer WRONG by the number of hours your GMT offset is. If you are in the Eastern U.S. like me, which is GMT -5, your result will have 5 hours added onto it. And if you try making DateCreated conform to UTC because julianday('now') goes against a GMT date:

SELECT julianday('now') - julianday(DateCreated, 'utc') FROM Payment;

This has a bug where it will add an hour for a DateCreated that is during Daylight Savings Time (March-November). Say that "now" is at noon on a non-DST day, and you created something back in June (during DST) at noon, your result will give 1 hour apart, instead of 0 hours, for the hours portion. You'd have to write a function in your application's code that is displaying the result to modify the result and subtract an hour from DST dates. I did that, until I realized there's a better solution to that problem that I was having: SQLite vs. Oracle - Calculating date differences - hours

Instead, as was pointed out to me, for dates stored in local time, make both match to local time:

SELECT julianday('now', 'localtime') - julianday(DateCreated) FROM Payment;

Or append 'Z' to local time:

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

Both of these seem to compensate and do not add the extra hour for DST dates and do straight subtraction - so that item created at noon on a DST day, when checking at noon on a non-DST day, will not get an extra hour when performing the calculation.

And while I recognize most will say don't store dates in local time in your database, and to store them in UTC so you don't run into this, well not every application has a world-wide audience, and not every programmer wants to go through the conversion of EVERY date in their system to UTC and back again every time they do a GET or SET in the database and deal with figuring out if something is local or in UTC.

Community
  • 1
  • 1
vapcguy
  • 7,097
  • 1
  • 56
  • 52
  • "It is only part of the picture, and a very simplistic answer, if you ask me." Yeah you're right. But my answer was given 3 minutes after he asked his question when yours comes over two years later. Simplistic but she seemed to suit him. – Fred Jan 16 '17 at 14:11
  • 4
    @Fred Fair enough. But it actually ended up taking me for a ride, as I described above, and so didn't help me. I wanted to be sure anyone seeing this in the future knew exactly what was going on so they didn't hit the same pitfalls I did - or, if they did, they'd know how to get out of them. – vapcguy Jan 18 '17 at 14:39
6

Just a note for writing timeclock functions. For those looking for hours worked, a very simple change of this gets the hours plus the minutes are shown as a percentage of 60 as most payroll companies want it.

CAST ((julianday(clockOUT) - julianday(clockIN)) * 24 AS REAL) AS HoursWorked

Clock In            Clock Out           HoursWorked
2016-08-07 11:56    2016-08-07 18:46    6.83333332836628
Gary
  • 119
  • 1
  • 5
6

Given that your date format follows : "YYYY-MM-DD HH:MM:SS", if you need to find the difference between two dates in number of months :

(strftime('%m', date1) + 12*strftime('%Y', date1)) - (strftime('%m', date2) + 12*strftime('%Y', date2))

Ashish Koshy
  • 131
  • 1
  • 7
4

If you want time in 00:00 format: I solved it like that:

SELECT strftime('%H:%M',
                CAST((julianday(FinishTime) - julianday(StartTime)) AS REAL),
                '12:00')
FROM something;
xamgore
  • 1,723
  • 1
  • 15
  • 30
Matas Lesinskas
  • 414
  • 6
  • 13
4

Firstly, it's not clear what your date format is. There already is an answer involving strftime("%s").

I like to expand on that answer.

SQLite has only the following storage classes: NULL, INTEGER, REAL, TEXT or BLOB. To simplify things, I'm going to assume dates are REAL containing the seconds since 1970-01-01. Here's a sample schema for which I will put in the sample data of "1st December 2018":

CREATE TABLE Payment (DateCreated REAL);
INSERT INTO Payment VALUES (strftime("%s", "2018-12-01"));

Now let's work out the date difference between "1st December 2018" and now (as I write this, it is midday 12th December 2018):

Date difference in days:

SELECT (strftime("%s", "now") - DateCreated) / 86400.0 FROM Payment;
-- Output: 11.066875

Date difference in hours:

SELECT (strftime("%s", "now") - DateCreated) / 3600.0 FROM Payment;
-- Output: 265.606388888889

Date difference in minutes:

SELECT (strftime("%s", "now") - DateCreated) / 60.0 FROM Payment;
-- Output: 15936.4833333333

Date difference in seconds:

SELECT (strftime("%s", "now") - DateCreated) FROM Payment;
-- Output: 956195.0
Stephen Quan
  • 21,481
  • 4
  • 88
  • 75
2

If you want difference in seconds

SELECT strftime('%s', '2019-12-02 12:32:53') - strftime('%s', '2019-12-02 11:32:53')
Sandris B
  • 133
  • 6
1

If you want records in between days,

select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0;
Rakesh Chaudhari
  • 3,310
  • 1
  • 27
  • 25
0

In my case, I have to calculate the difference in minutes and julianday() does not give an accurate value. Instead, I use strftime():

SELECT (strftime('%s', [UserEnd]) - strftime('%s', [UserStart])) / 60

Both dates are converted to unixtime (seconds), then subtracted to get value in seconds between the two dates. Next, divide it by 60.

https://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Fergal Moran
  • 4,525
  • 5
  • 39
  • 54
Yusril Maulidan Raji
  • 1,682
  • 1
  • 21
  • 46