android - 将 SQL Server 查询更改为 SQLite 中的等效查询

Question

我有这个在 SQL Server 中工作的查询:

Convert(datetime, [ChangedDate]) >= DATEADD(DAY, -3, CAST(GETDATE() AS DATE)) 

我想让它在 Android SQLite 数据库中工作。

据我所知，我需要使用类似:date('now','+14 day') 而不是 DATEADD，但它给了我一个datetime 错误(可能在 sqlite 中的 Convert(datetime,...) 处。

您可以修改此查询以使其在 SQLite 上运行吗？

Answer 1

SQLite 没有date 数据类型。所以您不需要使用 convert 或 cast。像这样的查询会起作用:

select  *
from    table1
where   col1 < datetime('now', '-3 days')

Example at SQL Fiddle.

有关详细信息，请参阅 SQLite manual :

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.