尝试从SQLite数据库获取上周，月份和年份记录时遇到问题。我的数据库表：
我只获得了今天，昨天，本周，本月和今年的唱片。
本周：
SELECT SUM(amount) AS Total, (substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date,1, 2)) AS currentDate FROM TransactionRec WHERE currentDate BETWEEN DATE('now', '-7 days') AND Date('now') GROUP BY currentDate
本月：
SELECT SUM(amount) AS Total, (substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date,1, 2)) AS currentDate FROM TransactionRec WHERE currentDate BETWEEN DATE('now', 'start of month') AND Date('now') GROUP BY currentDate
今年：
SELECT SUM(amount) AS Total, (substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date,1, 2)) AS currentDate FROM TransactionRec WHERE currentDate BETWEEN DATE('now', '-1 year') AND Date('now')GROUP BY currentDate
但是我不知道如何更改SQL语句以获取上周，月份和年份。有任何想法吗？
提前致谢。
最佳答案
您已经在使用日期修饰符。
只需再减去一周/月/年：
... BETWEEN date('now', '-14 days') AND date('now', '-7 days')
... BETWEEN date('now', 'start of month', '-1 months') AND date('now', 'start of month', '-1 days')
... BETWEEN date('now', '-2 years') AND date('now', '-1 years')