Search code examples
sqlsqlitesql-likespatialite

How to filter SQL by time (greater and less than)?

I want to query a subset from a dataset. Each row has a time stamp of the following format:

2014-04-25T17:25:14
2014-04-25T18:40:16
2014-04-25T18:44:57
2014-04-25T19:10:32
2014-04-25T20:22:12
...

Currently, I use the following query to select a time-based subset:

time LIKE '%2014-04-25T18%' OR time LIKE '%2014-04-25T19%'

This becomes quite complicated when you start to filter by mintutes or seconds.

Is there a way to run a query that such as ...

time > '%2014-04-25T18%' AND time < '%2014-04-25T19%'

A regular expression would be okay, too.

The database is a SpatiaLite database. The time column is of type VARCHAR.

Solution

  • Thanks to your posts and this answer I came up with this solution:

    SELECT * FROM data 
WHERE DATETIME(
  substr(time,1,4)||'-'||
  substr(time,6,2)||'-'||
  substr(time,9,2)||' '||
  substr(time,12,8)
) 
BETWEEN DATETIME('2014-04-25 18:00:00') AND DATETIME('2014-04-25 19:00:00');