SQLite select between dates

Tue, Oct 11, 2022 One-minute read

SQLite select between dates

SQLite has spartan types and today I learnt how to select between a date range.

SELECT date1,date2,amount,ROUND((JULIANDAY(date2) - JULIANDAY(date1))*86400) 
AS diff 
FROM times 
WHERE date1 between '2022-09-01' AND '2022-09-30';

Outputs

# .mode column
# .headers on
date1             date2             amount      diff
----------------  ----------------  ----------  ----------
2022-09-02 08:25  2022-09-02 17:11  1008.17     31560.0
2022-09-05 08:22  2022-09-05 17:05  1002.42     31380.0
2022-09-06 08:56  2022-09-06 16:30  870.17      27240.0
2022-09-07 08:34  2022-09-07 17:05  979.42      30660.0

Using JULIANDAY and multiplying by 86400 returns the difference in seconds between date1 and date2.

Last seven days

SELECT date1,date2,amount,ROUND((JULIANDAY(date2)-JULIANDAY(date1))*86400)
AS diff
FROM times
WHERE date1 > datetime('now', '-7 days');

Since start of the month

SELECT date1,date2,amount,ROUND((JULIANDAY(date2)-JULIANDAY(date1))*86400)
AS diff
FROM times
WHERE date1
BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime');`

Tags:

#sql