Query get Records between an initial and a final hour

Question

Query get Records between an initial and a final hour

Navigation

#1 by (2 votes)

1

I am trying to make a query to validate if a specific appointment already exists, since in the same period of time no other appointment should be generated. For this I consult the date of the appointment, the initial hour of the appointment and the final hour.

My dating table looks like this:

Then my query that more or less worked for me:

$query = $this->db->prepare("select * from events where ((e_time_start < ? AND e_time_end > ?) OR e_time_end > ? AND e_time_end < ? OR e_time_start > ? AND e_time_start < ?) AND e_date_event = ?;");
$query->execute([$time_start.':00', $time_end.':00', $time_start.':00', $time_end.':00', $time_start.':00', $time_end.':00', $date_start.'']);
$events = $query->fetchAll();

I should solve the following events regarding the information I have in the table but in some cases it does not solve them:

6am to 7am yes you should be able to add the appointment
7am to 8am should not be able to add the appointment
6am to 11am should not be able to add the appointment
8am to 11am should not be able to add the appointment
8am to 9am should not be able to add the appointment
9am to 10am should not be able to add the appointment
10am to 11am yes you should be able to add the appointment

mysql php pdo

asked by Christopher Flores 16.03.2018 в 04:21

source

1 answer

Read xml file and persist information every so often validate form with image if the field is empty?

score 2 · Accepted Answer

and see what is making this a bit crazy, there is a function called BETWEEN that performs a query by rank or is used to retrieve values within a range in a query

SELECT * FROM 'asistencias' WHERE 
fecha_inicial BETWEEN '2018-01-00 00:00:00' AND '2018-01-31 00:00:00' 
AND 
fecha_final BETWEEN '2018-01-00 00:00:00' AND '2018-01-31 00:00:00'

or you can try with

SELECT * from events where 
e_time_start  between '08:00:00' and '06:00:00' and
e_time_end between '08:00:00' and '06:00:00' 
AND e_date_event = '2018-03-16'
OR 
(e_time_start< '08:00:00' and e_time_end > '06:00:00' ) AND e_date_event = '2018-03-16'