Full months difference

Question

create table mytable (id integer not null,date_start TEXT,date_end TEXT,wanted_full_month INTEGER); 
insert into mytable (id, date_start, date_end, wanted_full_month)
values (1, '1992-09-15', '1992-11-14',1); /* Incomplete second month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (2, '1992-09-15', '1992-11-15',2); /* Complete second month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (3, '1992-09-15', '1992-10-14',0); /* Incomplete first month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (4, '1992-01-31', '1992-02-29',1);
/* It's the end of the month of date_end and the end of the month of date_start,
we take it as a complete month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (5, '1992-01-30', '1992-02-29',1);
/* It's the end of the month of date_end, it couldn't go longer,
we take it as a complete month */
SELECT *,floor((julianday(date_end) - julianday(date_start))/30) as wrong_full_months from mytable; as wrong_full_months from mytable;

How can I have a function like date_sub from DuckDB (documentation, source code) using SQLite? That is, getting the (irregular) difference of months like the column wanted_full_months (not a multiple of 30 days like in my example).

Does [this post](https://www.reddit.com/r/SQL/comments/je8x56/sqlite_solution_found_date_difference_in_years/) help you ? — MyICQ, Sep 06 '22 at 10:07
@MyICQ Hello, thanks. It doesn't help me because it's still a regular difference, of 365/12 days, quite alike my wrong month difference in my example (with a bit more precision). I want a full month irregular difference, quite alike the date_sub function in duckdb — Arnaud Feldmann, Sep 06 '22 at 10:10
So the "full month" is set on day ? What cases do you have around jan/feb/mar ? Example: 31 mar to 28 feb ? 28 feb to 28 mar ? 28 feb to 29 mar? It would be useful to update your question with desired output here. Note: date calculations are not trivial. I usually measure desired results by [timeanddate.com](https://www.timeanddate.com/date/durationresult.html?d1=31&m1=1&y1=1992&d2=28&m2=03&y2=1992). Here you can see difference in months. — MyICQ, Sep 06 '22 at 10:28
You are right. In fact, I try to convert a project from duckdb to SQLite and with your help I just found that their date_sub is written here : https://github.com/duckdb/duckdb/blob/d57a75443506a489656d008132cf127a88e7890b/src/function/scalar/date/date_sub.cpp They indeed do special cases if the ending date involves the last day of the month. I guess I'll have to code it myself and learn sqlite user-defined functions — Arnaud Feldmann, Sep 06 '22 at 11:19
SQLite does not support user-defined functions. Post sample data that cover all edge cases and expected results to clarify what you want. — forpas, Sep 06 '22 at 11:51
@forpas i've detailled my question. But Booboo has given me what I really needed, a way to write user-defined functions without going down to C-level — Arnaud Feldmann, Sep 06 '22 at 12:59

Booboo · Answer 1 · 2022-09-06T12:22:10.010

I would create a function that converts a date string into an absolute day by parsing out of the date the year, month and day and by pretending that every month has 31 days we compute:

absolute_day` = (year * 12 + month) * 31 + day

Then we can compute the month difference of two absolute dates using:

month_diff = floor((absolute_day1 - absolute_day2) / 31)

In Python, this would look like:

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row

def parse_date(date):
    """ Return [year, month, day] as a list of integers. """
    return list(map(lambda x: int(x), date.split('-')))

def absolute_day(year, month, day):
    # Based on no month having more than 31 days:
    return (year * 12 + month) * 31 + day

def month_diff(date1, date2):
    # Pretend every month has 31 days:
    day1 = absolute_day(*parse_date(date1))
    day2 = absolute_day(*parse_date(date2))
    return (day1 - day2) // 31

conn.create_function("MONTH_DIFF", 2, month_diff)

script = '''
create table mytable (id integer not null,date_start TEXT,date_end TEXT);
insert into mytable (id, date_start, date_end)
values (1, '1992-09-15', '1992-11-14');
insert into mytable (id, date_start, date_end)
values (2, '1992-09-15', '1992-11-15');
insert into mytable (id, date_start, date_end)
values (3, '1992-09-15', '1992-10-14');
'''

conn.executescript(script)
conn.commit()

rows = conn.execute('select id, date_start, date_end, month_diff(date_end, date_start) as nMonths from mytable').fetchall()
for row in rows:
    print(dict(row))

conn.close()

Prints:

{'id': 1, 'date_start': '1992-09-15', 'date_end': '1992-11-14', 'nMonths': 1}
{'id': 2, 'date_start': '1992-09-15', 'date_end': '1992-11-15', 'nMonths': 2}
{'id': 3, 'date_start': '1992-09-15', 'date_end': '1992-10-14', 'nMonths': 0}

You can also do this with PHP: https://www.php.net/manual/en/sqlite3.createfunction.php and with JavaScript: https://stackoverflow.com/questions/455292/is-it-possible-to-create-a-javascript-user-defined-function-in-sqlite — Booboo, Sep 06 '22 at 12:42

score 1 · Accepted Answer · answered Sep 07 '22 at 07:10

If you want a solution with SQLite code:

SELECT *,
       strftime('%Y', date_end, 'start of month', '-1 day') * 12 +
       strftime('%m', date_end, 'start of month', '-1 day') -
       strftime('%Y', date_start) * 12 -
       strftime('%m', date_start) +
       (strftime('%d', date_end, '+1 day') = '01'
        OR 
        strftime('%d', date_end) >= strftime('%d', date_start)
       ) full_month
FROM mytable;

See the demo.

Full months difference

2 Answers2