sql - Select all rows between two specific times across multiple days

Question

I'm managing an SQLite database containing messages along with associated timestamps collected over the past month, and I wish to select all of the entries, for all days, between two given hours. In pseudocode style: SELECT * FROM Messages BETWEEN x AND y, where x might be 14:45 and y 15:45, and returning all the messages between x and y for all the days over the past month.

Is there a straightforward way of performing this in SQLite?

Thanks in advance.

Answer 1

I think you are looking for something like this:

SELECT *
FROM MESSAGES
WHERE time(timestamp) >= time('14:45:00')
  AND time(timestamp) <= time('15:45:00')

Fiddle: http://sqlfiddle.com/#!9/cde2c/1/0

I took information from: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions