将sql server查询更改为sqlite

I'm converting a Sql Server query to sqlite and I am in the process of learning the SQLite queries. Regardless this is the one area that I am having a hard time with and that is the dates.

我正在将Sql Server查询转换为sqlite,我正在学习SQLite查询。无论这是我遇到困难的一个领域,那就是日期。

DATEADD(dd, 0, DATEDIFF(dd, 0, tblSomeTable.EventDate)) >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

What would the equivalent to this in sqlite be? Thank you in advance.

在sqlite中相当于什么?先感谢您。

The Event date field is a accepted datetime format - "YYYY-MM-DD HH:MM:SS.SSS"

事件日期字段是可接受的日期时间格式 - “YYYY-MM-DD HH:MM:SS.SSS”

5 个解决方案

#1

DateTime is stored as TEXT in SQLite, so you can just compare strings:

DateTime在SQLite中存储为TEXT,因此您只需比较字符串:

CREATE TABLE tblSomeTable (EventDate TEXT);
INSERT INTO tblSomeTable (EventDate) VALUES
    (strftime('%Y-%m-%d %H:%M:%f', 'now', '-1 day')),
    (strftime('%Y-%m-%d %H:%M:%f', 'now'          )),
    (strftime('%Y-%m-%d %H:%M:%f', 'now', '+1 day'));

SELECT EventDate,
       substr(EventDate, 1, 10),
       strftime('%Y-%m-%d', 'now'),
       substr(EventDate, 1, 10) >= strftime('%Y-%m-%d', 'now')
FROM tblSomeTable;

SELECT EventDate,
       substr(EventDate, 1, 10),
       date(),
       substr(EventDate, 1, 10) >= date()
FROM tblSomeTable;

The result in both cases (at the date of this edit :-) would be:

两种情况下的结果(在编辑之日:-)将是:

2016-01-14 12:34:56.789|2016-01-14|2016-01-15|0
2016-01-15 12:34:56.789|2016-01-15|2016-01-15|1
2016-01-16 12:34:56.789|2016-01-16|2016-01-15|1

#2

Assuming that the values in your table are stored in one of the supported date formats, you can use the date function to extract the date part of a timestamp:

假设表中的值以支持的日期格式之一存储,您可以使用日期函数来提取时间戳的日期部分:

date(tblSomeTable.EventDate) >= date('now')

#3

First thing I would say is that you shouldn't use that predicate in SQL Server, because enclosing columns in expression prevent SQL Server from use any index. If that predicate is the only one in the where clause then SQL Server would scan the entire table.

我要说的第一件事是你不应该在SQL Server中使用该谓词,因为在表达式中包含列会阻止SQL Server使用任何索引。如果该谓词是where子句中唯一的谓词,那么SQL Server将扫描整个表。

However the predicate can be rewritten in the following way:

但是,谓词可以通过以下方式重写:

tblSomeTable.EventDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND tblSomeTable.EventDate < DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))

In this way, if there is an index on EventDate column, SQL Server would use it if it covers the query or if it is selective enough.

这样,如果EventDate列上有索引,如果SQL Server覆盖查询或者它具有足够的选择性,它将使用它。

The same rule is applicable to SQLite.

同样的规则适用于SQLite。

Assuming EventDate is stored as ISO8601 string. The predicate can be written for SQLite like this:

假设EventDate存储为ISO8601字符串。可以像这样为SQLite编写谓词:

EventDate LIKE date('now') || '%'

If there is an index on EventDate column, SQLite can use it.

如果EventDate列上有索引,SQLite可以使用它。

How are your dates stored in SQLite? Could you post how SQLiteSpy show them?

你的日期如何存储在SQLite中?你能发布SQLiteSpy如何展示它们吗?

EDIT:

Oh! Sorry! I realized I miss the > part of >=. I saw = instead of >=. My answer were correct if the the operator was =. For >= my answer should be:

哦!抱歉!我意识到我错过了> =的部分。我看到=而不是> =。如果运算符是=,我的答案是正确的。对于> =我的答案应该是:

tblSomeTable.EventDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

For SQL Server. And:

对于SQL Server。和:

EventDate >= date('now')

For SQLite.

I'm getting older, sorry.

我变老了,抱歉。

#4

Sqlite3 does not have a date type for its Datatype list. Referencing the sqlite docs shows that sqlite has a few functions to manipulate times. I would imagine just converting to unix time as an integer would be your best bet.

Sqlite3的数据类型列表没有日期类型。引用sqlite文档表明sqlite有一些操作时间的函数。我想像转换为unix时间整数将是你最好的选择。

Edit. I would suggest an actual query to replace but I'm not nearly as familiar with sql as I am sqlite.

编辑。我会建议一个实际的查询来替换,但我不像sqlite那样熟悉sql。

#5

-1

I believe CL has already answered correctly. Might it be you stored the values as seconds since epoch (ie: Skype stores its datetime values like that in an SQLite table < g >).

我相信CL已经正确回答了。可能是您将值存储为自纪元以来的秒数(即:Skype将其日期时间值存储在SQLite表中)。

SELECT * from SomeTable 
  where date(eventdate, 'unixepoch', 'localtime') >= date()

#1