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.
This question is related to
sqlite
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
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
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.
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)
If you want records in between days,
select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0;
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))
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.
If you want records in between days,
select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0;
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.
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
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
If you want difference in seconds
SELECT strftime('%s', '2019-12-02 12:32:53') - strftime('%s', '2019-12-02 11:32:53')
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.
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.
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
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
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))
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.
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
If you want difference in seconds
SELECT strftime('%s', '2019-12-02 12:32:53') - strftime('%s', '2019-12-02 11:32:53')
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
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
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)
Source: Stackoverflow.com