Sqlite Compare Dates

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.)

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

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

SQLITE Date Comparison with different years

You are not using a proper date format.

SQLite wants dates in format yyy-mm-dd. What is happening with your code is that SQLite is doing string comparison. String-wise, '11/12/2019' is smaller than '4/12/2020' (because the former starts with '1' and the latter with '4'), but greater than '1/12/2020' (because '1/' is greater than '11').

I would urge you to convert your data to proper date formats. For this, you can do string manipulations:

update mytable set
    startDate = 
        substr(startDate, 7) 
        || '-' || substr(startDate, 4, 2) 
        || '-' || substr(startDate, 1, 2),
    endDate = 
        substr(endDate , 7) 
        || '-' || substr(endDate , 4, 2) 
        || '-' || substr(endDate , 1, 2)
;

Afterwards, you can do proper date comparisons, like:

startDate < '2020-12-01' -- or '2020-12-04'

SQLite-net-pcl Comparing Dates

Assuming that in [DatesCompleted] you store timestamps in ISO format YYYY-MM-DD hh:mm:ss, the correct SQLite syntax to get a timestamp exactly 7 days before the current timestamp is:

datetime('now', '-7 day')

or:

datetime('now', '-7 day', 'localtime')

to get the timestamp in local time.

so your query should be:

SELECT * 
FROM [Tasks] 
WHERE [Done] = 1 AND [DatesCompleted] >= datetime('now', '-7 day')

---

Related Topics