SQLite comparing time values

Question

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.

I get an error. Time argument as a string gets no error, but it returns records with a time of 1:00:12 — Nick JD, Feb 26 '15 at 00:57
Are the fields really in format `HH:MM:SS`, is the hour always two digits long? — Yirkha, Feb 26 '15 at 01:12
Ah! No the format can be H:MM:SS. How does that change things? Is it actually a string even though the SQLite field is DATETIME? — Nick JD, Feb 26 '15 at 01:16

score 1 · Accepted Answer · edited May 23 '17 at 12:06

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.

Thanks, Yirka! I will tighten up my formatting before the insert to sort this "datetime". — Nick JD, Feb 26 '15 at 02:40

SQLite comparing time values

1 Answers1