Difference between 2 dates in SQLite

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  select julianday datetime  now     2454788 09219907 sqlite gt  select datetime julianday datetime  now      2008-11-17 14 13 55

User · Answer

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

User · Answer

SELECT julianday  now   - julianday DateCreated  FROM Payment

User · Answer

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

User · Answer

If you want difference in seconds  SELECT strftime   s    2019-12-02 12 32 53   - strftime   s    2019-12-02 11 32 53

User · Answer

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

User · Answer

If you want difference in seconds  SELECT strftime   s    2019-12-02 12 32 53   - strftime   s    2019-12-02 11 32 53

User · Answer

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 gt  select julianday datetime  now     2454788 09219907 sqlite gt  select datetime julianday datetime  now      2008-11-17 14 13 55

User · Answer

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

User · Answer

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

User · Answer

If you want records in between days   select count col Name  from dataset where cast julianday  now  - julianday  Last updated  as int  lt  0

User · Answer

SELECT julianday  now   - julianday DateCreated  FROM Payment

User · Answer

SELECT julianday  now   - julianday DateCreated  FROM Payment

User · Answer

SELECT julianday  now   - julianday DateCreated  FROM Payment

User · Answer

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 gt  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

User · Answer

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 gt  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

User · Answer

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

User · Answer

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

User · Answer

If you want records in between days   select count col Name  from dataset where cast julianday  now  - julianday  Last updated  as int  lt  0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  select julianday datetime  now     2454788 09219907 sqlite gt  select datetime julianday datetime  now      2008-11-17 14 13 55

User · Answer

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 gt  select julianday datetime  now     2454788 09219907 sqlite gt  select datetime julianday datetime  now      2008-11-17 14 13 55

User · Answer

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

[sqlite] Difference between 2 dates in SQLite

Examples related to sqlite