Full months difference-CodePudding

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).

CodePudding user response：

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}

CodePudding user response：

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.