database 查询最后一天、上周、上个月 SQLite
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10504218/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query last day, last week, last month SQLite
提问by luismiyu
I have this table in my Android SQLite DB:
我的 Android SQLite 数据库中有这个表：
CREATE TABLE statistics (subject TEXT, hits INTEGER, fails INTEGER, date DATE)
On datefield is stored datetime('now', 'localtime')in every register.
在date字段中，储存datetime('now', 'localtime')在每个寄存器中。
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')
and this
和这个
SELECT * FROM statistics WHERE date BETWEEN datetime('now', localtime') AND datetime ( 'now', '-1 month')
and doesn't work :(
并且不起作用:(
How can I do it?
我该怎么做？
Can I check if the query is OK by simply forwarding date in the virtual device emulator?
我可以通过简单地在虚拟设备模拟器中转发日期来检查查询是否正常吗？
Thanks!
谢谢！
回答by luismiyu
I have found this solution. I hope it works for you.
我找到了这个解决方案。我希望这个对你有用。
For last day:
最后一天：
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');
For last month:
上个月：
SELECT * FROM statistics WHERE date BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime');
回答by Derek Larson
This code should get you the previous month
这个代码应该让你上个月
SELECT *
FROM statistics
WHERE date >= date('now','start of month','-1 month')
AND date < date('now','start of month')
回答by Victor Radulescu
SELECT *
FROM statistics
WHERE date >= date('now','start of month','-1 months')
AND date < date('now','start of month')
On more months, is "months"and not monthlike as other said before.
在更多的月份，是“月份”而不是像之前其他人所说的月份。
回答by Jesus Almaral - Hackaprende
You can create a calendar and then get the timestamp from it
您可以创建一个日历，然后从中获取时间戳
final Calendar todayCalendar = Calendar.getInstance();
final long todayTimestamp = todayCalendar.getTime().getTime();
todayCalendar.add(Calendar.DAY_OF_YEAR, -7);
final long aWeekAgoTimestamp = todayCalendar.getTime().getTime();
final String selection = TABLE_COLUMN_DATE_CREATED + " BETWEEN " + aWeekAgoTimestamp + " AND " + todayTimestamp;
回答by Issac Balaji
This code will bring previous week records hopefully
此代码有望带来上周的记录
SELECT * FROM order_master
WHERE strftime('%Y-%m-%d',om_date) >= date('now','-14 days') AND
strftime('%Y-%m-%d',om_date)<=date('now') order by om_date LIMIT 6
回答by Kirit Vaghela
SELECT
max(date(date, 'weekday 0', '-7 day')) WeekStart,
max(date(date, 'weekday 0', '-1 day')) WeekEnd,date
FROM table;
