SQLite Forum

v3.35 - wish for a datedif function
Login

v3.35 - wish for a datedif function

(1) By anonymous on 2020-12-16 14:15:09 [link] [source]

to return

1a. complete years number of rolling curtate 12 months periods
2a. complete months dd/mm/yyyy to dd/mm/yyyy 16/10/2000 to 16/11/2000 = 1 month
2b. complete months (gregorian calendar months)
3. complete days 

between 2 dates.

(2) By Warren Young (wyoung) on 2020-12-16 20:26:26 in reply to 1 [link] [source]

Your item 3 already exists. Number of whole days since the start of the month, for example:

  select cast(julianday('now') - julianday('now', 'start of month') as int);

I think the rest go beyond the "Lite" in SQLite, date math being so tricky once you get beyond things like Julian day arithmetic. If someone hasn't written an extension to provide such things, that's what I'd suggest you do rather than wait for SQLite to get such things.

(3) By anonymous on 2020-12-16 20:52:00 in reply to 2 [link] [source]

I made the suggestion for 3.35 since the agenda includes the provision of built in functions.

Item 3 in my list is the least useful since it is not possible to express it either as months or years with a simple arithmetic operation; I mentioned it simply as another output for the sake of completeness.

Lack of date arithmetic functions is one more thing that sets SQLite3 apart from other RDBMS.

(4) By Warren Young (wyoung) on 2020-12-16 21:00:04 in reply to 3 [link] [source]

the agenda includes the provision of built in functions.

You cannot reasonably conflate your wishes with exposing the platform's underlying libm or similar to the SQLite function interface. There is no cross-platform C-level way to do the things you want, so they'd have to be built into SQLite itself, not simply wrapped, as with the libm functions.

(The only reason libm wrappers weren't made earlier is because not all platforms targeted by SQLite have such a library, particularly small embedded systems.)

a simple arithmetic operation

Yelling increases the chance that a moderator will reject your message. Be warned.

Lack of date arithmetic functions is one more thing that sets SQLite3 apart from other RDBMS.

SQLite's differences are a large part of the reason it is more popular than, say, embedded MySQL.

(5.1) By Keith Medcalf (kmedcalf) on 2020-12-16 21:16:39 edited from 5.0 in reply to 3 [source]

Here is recursive SQL that will get you from one date to another date, going from the smaller date to the larger date:

with dates (startDate, endDate) as
     (
         values (?,?)
     ),
     yearsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select min(startDate, endDate),
                0,
                0,
                0,
                min(startDate, endDate),
                max(startDate, endDate)
           from dates
      union all
         select startDate,
                years + 1,
                months,
                days,
                date(startDate, printf('%+d years', years + 1)),
                endDate
           from yearsTable
          where resultDate < endDate
     ),
     monthsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from yearsTable
                    where resultDate <= endDate
                 order by years desc, months desc, days desc
                    limit 1
                )
      union all
         select startDate,
                years,
                months + 1,
                days,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months + 1)),
                endDate
           from monthsTable
          where resultDate < endDate
     ),
     daysTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from monthsTable
                    where resultDate <= endDate
                 order by years desc, months desc, days desc
                    limit 1
                )
      union all
         select startDate,
                years,
                months,
                days + 1,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months),
                                printf('%+d days', days + 1)),
                endDate
           from daysTable
          where resultDate < endDate
     ),
     dateDifference (startDate, resultDate, years, months, days) as
     (
         select  startDate,
                 resultDate,
                 years,
                 months,
                 days
            from daysTable
           where resultDate = endDate
     )
select years,
       months,
       days
  from dateDifference
;

You can do the same thing obversely to calculate how to get from the larger date to the smaller date (NB: The answers will be different in more than just sign).

(6) By anonymous on 2020-12-16 21:19:45 in reply to 5.0 [link] [source]

Thanks for this - I'll need to test it for myself.

As you clearly have great insight into SQLite and dates, can you comment on this statement, please:

An SQLite datetime value cast as real yields the number of days since noon in Greenwich on November 24, 4714 B.C.

Is this correct?

(8) By Keith Medcalf (kmedcalf) on 2020-12-16 21:34:21 in reply to 6 [link] [source]

No. Where did you find this?

The result of casting a text string containing a datetime string (to integer, real, or numeric) will be the year part of the text string since the embedded '-' is not valid in a number and the "conversion" will stop at this point.

There is a julianday(...) function which will take the "standard arguments" for specifying a datetime and return the days (floating point) since the julian epoch.

https://sqlite.org/lang_datefunc.html

The only actual datetime function is strftime and all the others are merely syntactic sugar of various sorts for this function.

That is, julianday(...) is merely syntactic sugar for cast(strftime('%J', ...) as real) carried out without all the bother of converting the result from floating-point to a text string and back to floating-point again. (The output of strftime is always text).

(9) By anonymous on 2020-12-16 21:39:41 in reply to 8 [link] [source]

No. Where did you find this?

On sqlitetutorial.org quite a few months ago & made a mental note to investigate at a later date (if I remember correctly, it was in a PDF that I downloaded (on another computer that packed up)) at it does not intuitively ring true and is awkward to test.

(11) By Keith Medcalf (kmedcalf) on 2020-12-16 21:50:08 in reply to 5.1 [link] [source]

Note that the bound parameters must be valid dates. If you want to ensure this then you need to change the values (?, ?) to values (date(?, '+0 days'), date(?, '+0 days')) which will result in "invalid" dates becoming valid dates or null.

If one of those is null then the rest of the query will return nothing.

(12) By Keith Medcalf (kmedcalf) on 2020-12-16 22:11:25 in reply to 11 [link] [source]

Example using "named parameters" going in both directions ...

-- find difference between :startdate and :enddate going from smaller to larger
with dates (startDate, endDate) as
     (
         values (date(:startdate, '+0 days'), date(:enddate, '+0 days'))
     ),
     yearsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select min(startDate, endDate),
                0,
                0,
                0,
                min(startDate, endDate),
                max(startDate, endDate)
           from dates
      union all
         select startDate,
                years + 1,
                months,
                days,
                date(startDate, printf('%+d years', years + 1)),
                endDate
           from yearsTable
          where resultDate < endDate
     ),
     monthsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from yearsTable
                    where resultDate <= endDate
                 order by years desc, months desc, days desc
                    limit 1
                )
      union all
         select startDate,
                years,
                months + 1,
                days,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months + 1)),
                endDate
           from monthsTable
          where resultDate < endDate
     ),
     daysTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from monthsTable
                    where resultDate <= endDate
                 order by years desc, months desc, days desc
                    limit 1
                )
      union all
         select startDate,
                years,
                months,
                days + 1,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months),
                                printf('%+d days', days + 1)),
                endDate
           from daysTable
          where resultDate < endDate
     ),
     dateDifference (startDate, resultDate, years, months, days) as
     (
         select  startDate,
                 resultDate,
                 years,
                 months,
                 days
            from daysTable
           where resultDate = endDate
     )
select years,
       months,
       days
  from dateDifference
;

-- find difference between :startdate and :enddate going from larger to smaller
with dates (startDate, endDate) as
     (
         values (date(:startdate, '+0 days'), date(:enddate, '+0 days'))
     ),
     yearsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select max(startDate, endDate),
                0,
                0,
                0,
                max(startDate, endDate),
                min(startDate, endDate)
           from dates
      union all
         select startDate,
                years - 1,
                months,
                days,
                date(startDate, printf('%+d years', years - 1)),
                endDate
           from yearsTable
          where resultDate > endDate
     ),
     monthsTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from yearsTable
                    where resultDate >= endDate
                 order by years, months, days
                    limit 1
                )
      union all
         select startDate,
                years,
                months - 1,
                days,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months - 1)),
                endDate
           from monthsTable
          where resultDate > endDate
     ),
     daysTable (startDate, years, months, days, resultDate, endDate) as
     (
         select *
           from (
                   select *
                     from monthsTable
                    where resultDate >= endDate
                 order by years, months, days
                    limit 1
                )
     union all
         select startDate,
                years,
                months,
                days - 1,
                date(startDate, printf('%+d years', years),
                                printf('%+d months', months),
                                printf('%+d days', days - 1)),
                endDate
           from daysTable
          where resultDate > endDate
     ),
     dateDifference (startDate, resultDate, years, months, days) as
     (
         select  startDate,
                 resultDate,
                 years,
                 months,
                 days
            from daysTable
           where resultDate = endDate
     )
select years,
       months,
       days
  from dateDifference
;

(7) By Richard Hipp (drh) on 2020-12-16 21:25:55 in reply to 1 [link] [source]

to return ... complete years number of rolling curtate 12 months periods

I'm guessing that the existing date/time functions within SQLite are easily able to handle this using the "+1 year" or similar modifier arguments. But that is just a guess because I don't understand what the OP is asking for.

(10) By anonymous on 2020-12-16 21:46:28 in reply to 7 [link] [source]

I was thinking of 'age last birthday' i.e. not just today's year less year of birth.

(13) By Richard Hipp (drh) on 2020-12-16 22:27:04 in reply to 10 [link] [source]

Here you go:

CREATE TABLE person(name TEXT, birthday DATE);
INSERT INTO person VALUES('xyz','1995-07-17');
SELECT name, (strftime('%Y%m%d','now') - strftime('%Y%m%d',birthday))/10000 AS age
  FROM person;