sqlite 整月差额

ki0zmccv 于 2022-11-15 发布在 SQLite

关注(0)|答案(2)|浏览(214)

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;

如何使用SQLite从DuckDB(documentation、source code)获得像date_sub这样的函数？也就是说，像列wanted_full_months(而不是我的例子中的30天的倍数)一样获得(不规则的)月差。

sqlite

来源：https://stackoverflow.com/questions/73620109/full-months-difference

2条答案

按热度按时间

m1m5dgzv1#

我将创建一个函数，通过解析出年、月和日的日期，并假装每个月都有我们计算的31天，将日期字符串转换为绝对日期：
absolute_day = (year * 12 + month) * 31 + day然后，我们可以使用以下命令计算两个绝对日期的月份差：month_diff = floor((absolute_day1 - absolute_day2) / 31)`
在Python中，这将如下所示：

#!/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()

打印：

{'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}

赞(0）回复(0）举报 2022-11-15

disbfnqx2#

如果您需要使用SQLite代码的解决方案：

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;

请参阅demo。

赞(0）回复(0）举报 2022-11-15

我来回答

sqlite 整月差额

2条答案

相关问题

热门标签

最新问答