查询最后一天，上周，上个月SQLite [英] Query last day, last week, last month SQLite

问题描述

我在我的Android SQLite数据库有这个表：

I have this table in my Android SQLite DB:

CREATE TABLE statistics (subject TEXT, hits INTEGER, fails INTEGER, date DATE)

在日期 datetime（'now'，'localtime'）

On date field is stored datetime('now', 'localtime') in every register.

现在我必须查询最后一天周和上月登记册显示一些统计数据。
我一直在尝试这样

Now I must query last day, last week and last month registers for showing some statistics. I've been trying something like this

    SELECT Timestamp, datetime('now', '-1 week') FROM statistics WHERE TimeStamp < datetime('now', '-1 week') 

和此

    SELECT * FROM statistics WHERE date BETWEEN datetime('now', localtime') AND datetime ( 'now', '-1 month')

且无效：（

我可以通过在虚拟设备模拟器中转发日期来检查查询是否可以？

Can I check if the query is OK by simply forwarding date in the virtual device emulator?

推荐答案

我找到了这个解决方案，希望它能为你工作。

I have found this solution. I hope it works for you.

对于最后一天：

SELECT * FROM statistics WHERE date BETWEEN datetime('now', 'start of day') AND datetime('now', 'localtime');

For last week:

SELECT * FROM statistics WHERE date BETWEEN datetime('now', '-6 days') AND datetime('now', 'localtime');

上个月：

SELECT * FROM statistics WHERE date BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime');

这篇关于查询最后一天，上周，上个月SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！