How to Compare SQLite Timestamp Values

How to compare sqlite TIMESTAMP values

The issue is with the way you've inserted data into your table: the +0200 syntax doesn't match any of SQLite's time formats:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

Changing it to use the SS.SSS format works correctly:

sqlite> CREATE TABLE Foo (created_at TIMESTAMP);
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56+0200');
sqlite> SELECT * FROM Foo WHERE foo.created_at < '2010-05-28 16:20:55';
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56.200');
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
2010-05-28T15:36:56.200

If you absolutely can't change the format when it is inserted, you might have to fall back to doing something "clever" and modifying the actual string (i.e. to replace the + with a ., etc.).

---

(original answer)

You haven't described what kind of data is contained in your CREATED_AT column. If it indeed a datetime, it will compare correctly against a string:

sqlite> SELECT DATETIME('now');
2010-05-28 16:33:10
sqlite> SELECT DATETIME('now') < '2011-01-01 00:00:00';
1

If it is stored as a unix timestamp, you need to call DATETIME function with the second argument as 'unixepoch' to compare against a string:

sqlite> SELECT DATETIME(0, 'unixepoch');
1970-01-01 00:00:00
sqlite> SELECT DATETIME(0, 'unixepoch') < '2010-01-01 00:00:00';
1
sqlite> SELECT DATETIME(0, 'unixepoch') == DATETIME('1970-01-01 00:00:00');
1

If neither of those solve your problem (and even if they do!) you should always post some data so that other people can reproduce your problem. You should even feel free to come up with a subset of your original data that still reproduces the problem.

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 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 compare timestamp with DateTime in C#

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.

Source: sqllite.org

My suggestion would be to just save the datetime values as integer. This way you will have no problems with operations such as comparision. And if you ever need non-numeric date in your query, you can use built in Datetime function like this:

SELECT datetime(1092941466, 'unixepoch');

Sqlite : Comparing timestamp value stored in a column to current Date

If you want current date then use the following query :

 SELECT date('now');

To get all columns in your table having ActionDate equal to todays date use the below query :

select * from table where strftime('%Y-%m-%d ', datetime(ActionDate, 'unixepoch'))  = date('now)' ;

more info on SQLite Date & Time queries can be found here

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 query by timestamp and value

I think that you want this:

select count(distinct value) = 1 result
from tablename
where id <= ? 
and timestamp - (select timestamp from tablename where id = ?) <= 60;

Replace the ? placeholder with the id that you want the results for.

Maybe you want the absolute value of the difference of the timestamps to be less than 60, so if this is the case then change to:

and abs(timestamp - (select timestamp from tablename where id = ?)) <= 60;

See the demo.

---

Related Topics