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.
Difference between 2 dates in SQLite
Admin
1
Viewed 173167
12 Answers
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
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
If you want difference in seconds
SELECT strftime('%s', '2019-12-02 12:32:53') - strftime('%s', '2019-12-02 11:32:53')
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.
Related