CODEWITHSUNDEEP
sqlitetimestamp
Sim
Sim

Reputation: 4185

sqlite3 behavior comparing time(string)s

I am trying to select all times after a given time:

SELECT created FROM table WHERE created >= '2014-11-11 00:00:00';

but I get back results including:

2014-11-6
2014-11-3
ect..

which prompted me to do some tests which confused me:

SELECT '2014-11-11' < '2014-11-3';
1
sqlite> SELECT DATETIME('2014-11-11') < '2014-11-3';
1
sqlite> SELECT DATETIME('2014-11-11') < '2014-11-03';
0
sqlite> SELECT DATETIME('2014-11-11') < DATETIME('2014-11-3');
;;no result

it seems that sqlite cannot compare two datetime and using any other combination results in two strings being compared which is not what I want. Which leads me to the question:

How does sqlite3 behave towards 'timestamps' and how to store and compare them properly so that they behave as one would expect?

I am using Ubuntu Linux 14.04 with sqlite3 3.8.2 2013-12-06 14:53:30

Upvotes: 0

Views: 177

Answers (1)

user4003407
user4003407

Reputation: 22102

There is no timestamp type in SQLite. So, all of your tests are string comparison: DATETIME('2014-11-11') returns 2014-11-11 00:00:00, and DATETIME('2014-11-3') returns null since 2014-11-3 is not a format that DATETIME understands. Your last case return null, but not no result.
If you want to SQLite compare your timestamps properly, than you should store them in sortable format: 2014-11-03 instead of 2014-11-3. And if you want to use SQLite date and time functions, than that should be format that them understands.

Upvotes: 2

Related Questions