SQL where time in timestamp

Question

I have records like:

2017-07-24 16:59:32
2017-07-24 17:53:38
2017-07-24 22:26:08
2017-07-24 23:04:54
2017-07-25 08:33:43
2017-07-25 10:06:47

And I want to write an sql query which compares only the time part of timestamp, as example:

SELECT * FROM table WHERE date BETWEEN '17:30:00' and '22:30:00'

Check only the time and ignore the date. Is it possible to do so?

I'm using MySQL

SELECT * FROM table WHERE hour('date') = 9;

Takes full hour, as I sometimes need to take only half of an hour.

Answer 1

You can convert the DATETIME to a time and use that in the comparison.

Given the sample code:

CREATE TABLE Table1
(
  Column1 VARCHAR(50),
  Column2 VARCHAR(50),
  Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO
Table1
(Column1, Column2, Timestamp)
VALUES
('Valid', 'Time', '2017-01-01 17:43:01'),
('Invalid', 'Time', '2017-01-01 16:00:43');

You could query it like the following:

SELECT
  *,
  DATE_FORMAT(`timestamp`, '%H:%i:%s') AS Time
FROM
  Table1
WHERE
  DATE_FORMAT(`timestamp`, '%H:%i:%s') BETWEEN '17:30:00' AND '22:30:00';

SQL Fiddle

Answer 2

This should work:

SELECT * FROM table
WHERE
(HOUR(date) BETWEEN 18 AND 21) OR
(HOUR(date) = 17 AND MINUTE(date)>=30) OR
(HOUR(date) = 22 AND MINUTE(date)<=30);

Or another approach would be to convert to DATE, add the hours and minutes and then use between.

SELECT * FROM table
WHERE date BETWEEN
ADDDATE(ADDDATE(DATE(date), INTERVAL 17 HOUR), INTERVAL 30 MINUTE)
AND
ADDDATE(ADDDATE(DATE(date), INTERVAL 22 HOUR), INTERVAL 30 MINUTE);