java - SQL语句获取上周、月份和年份记录

Question

我在尝试从 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 语句来获取最近周、月和年。有什么想法吗？

提前致谢。

Answer 1

您已经在使用日期修饰符。只需再减去一周/月/年:

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