Comparison Datetime in scheduling in the database

Question

I need a light. I am creating a car travel scheduling system and I need that before including a new trip, it be checked whether the date and time of the trip and the date and time of the trip were no longer taken, using that car that’s like FK and that driver that’s like FK.

Or it is necessary that before the trip is included, know if that vehicle And the driver And the date and time are available.

Answer 1

I will assume that :data_inicio and :data_fim be the timestamps of the trip you want to mark, with the id driver :idm and vehicle :idv.

I will also assume that your tables are viagem, motorista and viatura, whereas motorista and viatura have a field id each being a primary key and viagem have the fields id_motorista and id_veiculo. On the table viagem we also have the columns inicio and fim, which are timestamps. All these fields are NOT NULL.

SELECT g.*
FROM viagem g
INNER JOIN motorista m ON g.id_motorista = m.id
INNER JOIN viatura v ON g.id_viatura = v.id
WHERE (m.id = :idm OR v.id = :idv)
AND NOT (g.inicio > :data_fim)
AND NOT (g.fim < :data_inicio)

This query will bring as a result the trips that will be colliding with the one you want to register. It works looking for trips with:

• The same driver OR the same car.

• That do not start after the date/ time that ends the trip you want to register

• That do not end before the date/ time that begins the trip you want to register.

These last two conditions are the most important part, because if the trip you want to register ends before starting already registered or begins after finishing already registered, then it does not collide. Otherwise, there must have been a collision of schedules.