CODEWITHSUNDEEP
sqlite
Nick JD
Nick JD

Reputation: 47

SQLite comparing time values

I've got a time field (HH:MM:SS) in a table and need a query that limits results to all records where the time field is more than 3:00:00

Pretty sure I need to use the time() function somehow, but no matter what I try I either get an error or incorrect resultsets.

select * from table where time_happens > '03:00:00'

...doesn't give an error, but doesn't work correctly.

Upvotes: 2

Views: 1886

Answers (1)

Yirkha
Yirkha

Reputation: 13818

You noted that the values can sometimes be stored without the leading zero (e.g. 1:00:12). This creates problems.

SQLite does not have special date/time data types. It allows date/time values to be stored in the basic types like TEXT (more info), but then it expects them to be in one of the predefined formats only. There is HH:MM:SS in the list, but no H:MM:SS, so it would never understand your values, even if you used some date/time functions:

sqlite> SELECT TIME('12:34:56');
12:34:56
sqlite> SELECT TIME('2:34:56');
 

But you don't actually need to make SQLite understand your values here. If you pad them correctly (when inserting, by an UPDATE, or dynamically during the SELECT), it will work even without using any functions, because plain string comparisons are enough:

SELECT * FROM table WHERE SUBSTR('0'||time_happens,-8,8) > '03:00:00';

Note I needed to use the SQLite padding workaround.
Also you will still get the unpadded values in the result.

Upvotes: 1

Related Questions