CODEWITHSUNDEEP
mysqlsql
ro ko
ro ko

Reputation: 2986

Get an hour of time period between given minimum and maximum time period

I have a table time_period,

CREATE TABLE `time_period` (
  `id` int(11) NOT NULL,
  `time` time NOT NULL,
  `slots` int(11) NOT NULL
) ;

The data in the table is basically time period in a 24 hour clock at the interval of 5 minutes. Something like this:

INSERT INTO `time_period` (`id`, `time`, `slots`) VALUES
(1, '00:00:00', 3),
(2, '00:05:00', 3),
(3, '00:10:00', 3),
(289, '24:00:00', 3);

Live test: http://rextester.com/live/UXPV82172

What I am trying to achieve is, get an hour of time between min_time and max_time (I am concatenating same date as date given in min_time and max_time) ,given that the current_time (actual current date and time).

EDIT: updated the greater than less than sign, had an error here there (sorry)

SELECT 
    t0_.id AS id_0, 
    t0_.time AS time_1, 
    t0_.slots AS slots_2 
FROM time_period t0_ WHERE 
    '2018-03-18' + t0_.time >= '2018-03-18 19:09' AND # :min_time
    '2018-03-18' + t0_.time <= '2018-03-18 20:09' AND # :max_time
    '2018-03-18' + t0_.time >= '2018-03-17 21:05' # :current_time
    ORDER BY t0_.time ASC LIMIT 20

Looked pretty straight forward but for some reason all I get is:

1,00:00:00, 3

EDIT

Expected output must be same as when running sql:

SELECT 
t0_.id AS id_0, 
t0_.time AS time_1, 
t0_.slots AS slots_2 
FROM time_period t0_ WHERE 
t0_.time >= '19:09:00' AND
t0_.time <= '20:09:00';

i.e.

1 245 19:10:00 3

2 246 19:15:00 3

3 247 19:20:00 3... and so on

Could someone help me explain this please.

Upvotes: 1

Views: 77

Answers (2)

ro ko
ro ko

Reputation: 2986

Apparently I just had to concatenate my date and time field to get datetime field for comparison.

SELECT 
    t0_.id AS id_0, 
    t0_.time AS time_1, 
    t0_.slots AS slots_2,
    concat('2018-03-18', " ", t0_.time) as x
FROM time_period t0_ WHERE 
    concat('2018-03-18', " ", t0_.time) >= '2018-03-18 19:09:00'
    AND
    concat('2018-03-18', " ", t0_.time) <= '2018-03-18 20:09:00'
    AND
    concat('2018-03-18', " ", t0_.time) >= '2018-03-17 21:05'        
    ORDER BY t0_.time ASC LIMIT 50

Upvotes: 0

A Franklin
A Franklin

Reputation: 121

You need to build them into datetimes if you want to compare them. 

   CONVERT(DATE, '2018-03-18') +    
   t0_.time >= CONVERT(DATETIME, 
   '2018-03-18 19:09') AND # :min_time
   #etc

Upvotes: 1

Related Questions