How to compare hours and minutes in SQLite?

Question

I have a users "hours worked" stored in SQLite as `HH:MM` in my Android application. I do not want to change the way this data is stored, if possible. That would be a nightmare. I want them to be able to search entries in which their time is `=`,`<`, or `>` another timestamp. For example, using my app they can make a `WHERE` clause like the following: `time(Hours) > time('5:51')` where `Hours` is the column name. However, I can't seem to get any results, even if I know there's a matching row. Am I doing something wrong? I don't get any exceptions, just no results.

Accepted Answer

time will not accept durations unless they are coincidentally also valid times. So even though someone can work 40 hours:

```
SELECT (time('40:00') IS NULL);
```

gives 1 (showing it's an error).

```
SELECT (time('11:00') IS NULL);
```

is 0. But presumably both need to work.

Luckily, if you stick to two-digit hours and two-digit minutes, you can compare them as ordinary strings.

```
SELECT '84:13' > '29:03';
1

SELECT '05:12' > '43:58';
0
```

EDIT: A better solution might be to simply store the number of minutes as an INTEGER (one of the SQLite base types). Yes, you need to do a one-time upgrade conversion, but I think it's a better choice.

You have complete precision, you can easily multiply (e.g. by an hourly rate), and it's a simple modulus if you want to display as HH:MM in some report.

How to compare hours and minutes in SQLite?

Solution

Problem