How do I measure the difference between two dates in sqlite?

Question

I'm new to SQL and could really use some help.

I've got an 'arrival_date' column and a 'departure_date' column in format dd/mm/yyy as TEXT. I'd like to know how many days are in between each but I have a few thousand data point so some manual input solutions I've found are not appropriate. I also know the format of my dates isn't right.

Not sure it is possible to do on the lite version. What do you think?

https://stackoverflow.com/a/289686/1030675 – choroba Dec 01 '21 at 15:28 — choroba, Dec 01 '21 at 15:28

score 0 · Answer 1 · edited Dec 01 '21 at 15:54

First of all, stocking dates as a string is not a good idea.

Maybe there is a better solution for this but for me, you can try to get substrings of your date, determine the year of the date and reconstitute your dates and take the difference between two dates.

Here is an example :

For example, the arrival date is 14/12/021 // as your date format is dd/mm/yyy
And the departure date is 18/12/021


to calculate the diff :

    SELECT
     CAST(
       JULIANDAY(
         DATE(
              2 ||
              SUBSTR('18/12/021', 7) || '-' || 
              SUBSTR('18/12/021', 4,2) || '-' || 
              SUBSTR('18/12/021', 0,3) 
             )) -
       JULIANDAY(
         DATE(
              2 ||
              SUBSTR('14/12/021', 7) || '-' || 
              SUBSTR('14/12/021', 4,2) || '-' || 
              SUBSTR('14/12/021', 0,3) 
             ))
          AS INTEGER)

the result is 4. Instead of 18/12/021, you should use your departure date and the same for the arrival date.

For the date diff you can refer to Difference between 2 dates in SQLite

How do I measure the difference between two dates in sqlite?

1 Answers1