Sqlite Datetime Comparison

SQLite DateTime comparison

SQLite doesn't have dedicated datetime types, but does have a few datetime functions. Follow the string representation formats (actually only formats 1-10) understood by those functions (storing the value as a string) and then you can use them, plus lexicographical comparison on the strings will match datetime comparison (as long as you don't try to compare dates to times or datetimes to times, which doesn't make a whole lot of sense anyway).

Depending on which language you use, you can even get automatic conversion. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)

Sqlite datetime comparison with a unix timestamp

You use CURRENT_TIMESTAMP when inserting new rows.

This means that in your column the values are not stored as unix timestamps becuase CURRENT_TIMESTAMP returns the current date in the format of YYYY-MM-DD hh:mm:ss.

You can transform the unix timestamp to datetime in the format of YYYY-MM-DD hh:mm:ss with the function datetime() and the unixepoch modifier:

conn.execute("SELECT * FROM data WHERE datetime <= datetime(?, 'unixepoch')", (ts, ))

If your unix timestamp contains milliseconds you must strip them off:

conn.execute("SELECT * FROM data WHERE datetime <= datetime(? / 1000, 'unixepoch')", (ts, ))

Or, you can transform the string datetime in the column datetime to a unix timestamp with the function strftime():

conn.execute("SELECT * FROM data WHERE strftime('%s', datetime) + 0 <= ?", (ts, ))

If you want to store integer values in the column, use strftime() like this:

INSERT INTO data VALUES (strftime('%s', CURRENT_TIMESTAMP) + 0, 'hello')

How to compare two dates in SQLite?

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.

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.

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.

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';

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 compare dates

From the documentation:

SQLite does not have a storage class set aside for storing dates and/or times.

So your column isn't exactly stored as a date. Reading further, we learn that columns specifed as DATE are actually stored as NUMERIC using affinity rule 5.

Going back up to section 1.2:

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.

Good. So let's try:

SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum 
    BETWEEN julianday('1993-01-01') AND julianday('2000-01-01');

Oddly enough, SQL Fiddle seems to store DATEs as strings and the above doesn't work. In this case, the following should:

SELECT Geburtsdatum FROM Kunde
WHERE date(Geburtsdatum)
    BETWEEN date('1993-01-01') AND date('2000-01-01');

Additionally, in your case you seem to be getting some strange (read: localized) format returned. I wonder if it really is a string in your case too, just with a different format. You could try:

SELECT Geburtsdatum FROM Kunde
WHERE strftime('%d.%m.%Y', Geburtsdatum)
    BETWEEN date('1993-01-01') AND date('2000-01-01');

Why does this Sqlite date comparison work?

The ISO 8601 date/time format YYYY-MM-DD HH:MM::SS is specifically designed to permit lexicographic sorting that corresponds to chronological sort order. So, comparisons between strings in this format work because that's the way it is designed to work.

According to the SQLite docs, whenever 2 strings are compared, the applicable collation sequence is used to compare the 2 strings. The collation sequence used depends on the following rules:

7.1. Assigning Collating Sequences from SQL

Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column.

The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows:

If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.

If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators is still considered a column name.

Otherwise, the BINARY collating function is used for comparison.

Note that storing date/time values as strings in any other format that does not compare the same lexicographically as chronologically results in date/time comparisons that are usually wrong.

SQLite DateTime Comparison With VARCHAR

dd/mm/yyyy is not a valid TimeString in SQLite.

The correct format is yyyy-mm-dd.

As reported here,
valid TimeStrings in SQLite are:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

And you don't need to cast to... date (which is not an existing data type in SQLite, and it will fall back to TEXT).

Reference on SQLite DataTypes: https://www.sqlite.org/datatype3.html

Also note that the semicolon (;) at the end of queries and commands is completely useless, since SQLite won't concatenate SQL statements.

In the end, your query should really be

SELECT * FROM tblTest WHERE testDate BETWEEN '2015-09-28' AND '2015-10-10'

SQLite datetime comparison with 'now' in ISO8601

You could convert the database values into the same format:

SELECT * FROM MyTable WHERE datetime(date) > datetime('now');

However, it would be a better idea to convert the comparison value into the database format (a direct access of the column value makes it possible to use indexes):

SELECT * FROM MyTable WHERE date > strftime('%Y-%m-%dT%H:%M:%S', 'now');

Sqlite query, compare datetime field with current date time

Try select task_dateTime from tasks where task_dataTime < CURRENT_TIME;