CODEWITHSUNDEEP
sqlsqlite
Ewa Szyszka
Ewa Szyszka

Reputation: 69

Difference between two dates in Sqlite3

I work in Sqlite3 and tried 

DATEDIFF(days, listing_date, sale_date)

But it didn't work. What is the correct way to take a difference between two columns which are timestamps in Sqlite3?

id  listing_date    sale_date
1   2012/02/27      2020/02/27
2   2011/06/27      2020/02/17
3   2008/08/03      2020/02/19
4   2012/12/27      2020/02/07
5   2001/02/17      2020/02/29
6   2019/02/01      2020/02/27
7   2018/02/27      2020/02/03

Upvotes: 2

Views: 2064

Answers (1)

forpas
forpas

Reputation: 164089

You can use julianday():

SELECT 
  julianday(replace(sale_date, '/', '-')) - julianday(replace(listing_date, '/', '-'))
FROM tablename

You will get the difference in days as a real number.
If your date columns contain a time part then you may get results with decimal places like: 5.1234 which you can round() or cast to integer values.
The replacement of / with - is necessary if you store the dates in the format in your question.

Upvotes: 5

Related Questions