安卓：保存日期通常为sqlite的，但按日期排序考虑月份和日期 [英] Android: Save date normally to sqlite but sort by date considering month and day

问题描述

我看到一篇关于存储日期的数量。

I have seen number of post about storing date.

我仍然没有得到它保存到一个SQLite数据库的罚款和准确的方法。

I am still not getting the fine and exact approach about saving it to a sqlite database.

我能够储存，但在排序过程中我需要考虑只月份和日期，就像生日多年的地方并不重要。

I am able to store it, but during sorting I need to consider only month and day just like birthday where years doesn't matter.

会有什么查询，如果我想，如果搜查2月28日？

What will be the query if I want to get the row whose date is 2 or 3 days in advance, like 2nd march row if searched on 28 Feb?

推荐答案

您应该检查出的日期和放大器;时间函数的。

例如，为了解决你的问题和会是怎样的查询，如果我要得到的日期提前2〜3天行你会使用儒略日计算，例如正如这个例子，你可以在的sqlite3 外壳直接执行：

For instance, to solve your problem "And what will be the query if i want to get the row whose date is 2 or 3 days in advance" you'd use julian day calculations, such as this example that you can execute directly in the sqlite3 shell:

create TABLE example (_id INTEGER PRIMARY KEY NOT NULL, date TEXT NOT NULL);
insert into example (date) values ('2011-01-02');
insert into example (date) values ('2011-04-02');
insert into example (date) values ('2012-02-26');
insert into example (date) values ('2012-02-27');
insert into example (date) values ('2012-02-28');
insert into example (date) values ('2012-02-29');
insert into example (date) values ('2012-03-01');
insert into example (date) values ('2012-03-02');
insert into example (date) values ('2012-03-03');
select date from example where julianday(date) - julianday('now') < 3 AND julianday(date) - julianday('now') > 0;

这将返回（考虑到今天是2月28日）的日子是一，二，三天的未来：

This would return (given that "today" is feb 28th) all the days that are one, two or three days in the future:

2012-02-29
2012-03-01
2012-03-02

编辑：要只返回行，而不管一年，你可以做这样的事情 - 用查看（再次为例直接SQLite中）：

To only return rows, regardless of year, you could do something like this - using a VIEW (again, exampl is directly in SQLite):

create view v_example as select _id, date,
    strftime("%Y", 'now')||'-'||strftime("%m-%d", date) as v_date from example;

本查看将返回日期和放大器;在你的数据库倍本年度的重订 - 这当然可以引进靠不住的行为的所有方式与闰年

This VIEW would return the date & times in your database "rebased" on the current year - which, of course could introduce all manner of wonky behavior with leap years.

您可以选择所有像这样在这种情况下，日期：

You can select all the dates like this in that case:

select date from v_example where 
    julianday(v_date) - julianday('now') < 3 AND
    julianday(v_date) - julianday('now') > 0 ORDER BY v_date;

这将返回：

2012-02-29
2012-03-01
2001-03-01
2012-03-02
2010-03-02

这篇关于安卓：保存日期通常为sqlite的，但按日期排序考虑月份和日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！