如何比较 SQLite 中的两个日期? [英] How to compare two dates in SQLite?

问题描述

我有点假设它是一个字符串，所以我将它作为一个字符串进行比较，但并不奇怪它失败了.我相信这就是它在 Mysql 中的工作方式.我可能是错的，因为我有一段时间没有工作了.在任何一种情况下，如何检查 SQLite 中的日期是否相等?我将在 WHERE 子句中使用它.

I kind of assumed it was a string, so I compared it as a string, but not surprisingly it failed. I believe thats how it works in Mysql. I could be wrong as I haven't worked on it in a while. In either case, how can I check if dates are equal in SQLite? I will be using it in a WHERE clause.

SELECT a._id, b._id, b.start_date,a.event_name, b.start_time, 
b.end_date, b.end_time, b.location FROM events_info b INNER JOIN events a ON
 a._id=b.event_id WHERE b.start_time = '6:00';

(添加空格以便于查看)

(added space to make it easier to look at)

推荐答案

SQLite 没有专用的 DATETIME 类型.通常人们所做的是确保他们将日期存储为一致的格式化字符串；例如，YYYY-MM-DD hh:mm:ss.如果这样做，只要保持一致，那么您就可以直接比较日期:

SQLite doesn't have a dedicated DATETIME type. Normally what people do is make sure they store the date as a formatted string that is consistent; for example, YYYY-MM-DD hh:mm:ss. If you do so, as long as you're consistent, then you can compare dates directly:

SELECT * FROM a WHERE q_date < '2013-01-01 00:00:00';

这是有效的，因为即使比较在技术上是按字母顺序进行比较而不是数字比较，日期的格式也一致，例如按字母顺序和数字顺序排序.

This works because even though the comparison is technically an alphabetical comparison and not a numeric one, dates in a consistent format like this sort alphabetically as well as numerically.

对于这样的模式，我建议以 24 小时格式存储日期(上面的例子是午夜).用零填充月、日和小时.如果您的日期将跨越多个时区，请将它们全部存储在 UTC 中，并在客户端执行您需要的任何转换以将它们转换为本地时区.

For such a schema, I would suggest storing dates in 24-hour format (the above example is midnight). Pad months, days, and hours with zeros. If your dates will span multiple timezones, store them all in UTC and do whatever conversion you need client-side to convert them to the local time zone.

通常日期和时间都存储在一列中.如果您出于某种原因必须将它们分开，只需确保您的日期和时间都一致.例如，日期应全部为 YYYY-MM-DD，时间应全部为 hh:mm:ss.

Normally dates and times are stored all in one column. If you have to have them separated for whatever reason, just make sure you dates are all consistent and your times are all consistent. For example, dates should all be YYYY-MM-DD and times should all be hh:mm:ss.

YYYY-MM-DD hh:mm:ss 是首选格式的原因是因为当您从最大日期间隔(年)到最小日期间隔(秒)时，您可以非常轻松地对它们进行索引和排序，并且使用高性能.

The reason that YYYY-MM-DD hh:mm:ss is the preferred format is because when you go from the largest date interval (years) to the smallest (seconds), you can index and sort them very easily and with high performance.

SELECT * FROM a WHERE q_date = '2012-06-04 05:06:00';

将使用索引来确定日期/时间，而不必进行全表扫描.或者，如果它们位于两个单独的行中:

would use the index to hone in on the date/time instead of having to do a full table scan. Or if they're in two separate rows:

SELECT * FROM a WHERE q_date = '2012-06-04' AND q_time = '05:06:00';

关键是要确保进入数据库的日期和时间格式一致.为了用户友好的演示，所有转换都在客户端进行，而不是在数据库中.(例如，将2012-06-04 05:06:00"转换为1:06am Eastern 6/4/2012".)

The key is to make sure that the dates and times are in a consistent format going into the database. For user-friendly presentation, do all conversion client-side, not in the database. (For example, convert '2012-06-04 05:06:00' to "1:06am Eastern 6/4/2012".)

如果这不能回答问题，请您发布您用来存储日期和时间的确切格式，以及您尝试比较但未按预期方式工作的两个示例日期他们去?

If this doesn't answer question, could you please post the exact format that you're using to store your dates and times, and two example dates that you're trying to compare that aren't working the way you expect them to?

这篇关于如何比较 SQLite 中的两个日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！