Home > OS >  Filtering data by time only in datetime column for sql
Filtering data by time only in datetime column for sql

Time:08-17

Working with sqlite in Python.

I have data timestamped

2022-05-22 00:57:28
2022-05-22 14:40:18
2022-05-22 02:58:32
2022-05-22 03:59:30
2022-05-22 15:07:17

I would like to get all rows that fall in between a TIME range.

This produces no results

SELECT *
FROM speedtests 
WHERE date_run BETWEEN time("00:00:00") AND time("06:00:00")

I tried the solution from a similar question, however it returns every single result in the database.

select * from table
where cast(date_run as time) >= '00:00:00'
   OR cast(date_run as time) <= '06:00:00'

I have also tried GROUP BY statements but could not get it to work.

If getting individual rows is not possible, the max, min, avg, count values are enough for the filtered dataset - aggregate statement which I also tried with no luck.

CodePudding user response:

SELECT * FROM Table_Name WHERE time(Column_Name) BETWEEN ('14:30:00') AND ('14:55:00')

Above Query is to filter the data based on time, Replace Table_Name with your table name and Column_Name with your column name for what you want to filter the data.

enter image description here

  • Related