0
Follow
0
View

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

legend03u8z 注册会员
2023-01-25 04:38

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

About the Author

Question Info

Publish Time
2023-01-25 04:38
Update Time
2023-01-25 04:38