CODEWITHSUNDEEP
mysqlsqldatabase
Sorin Adrian Carbunaru
Sorin Adrian Carbunaru

Reputation: 618

How to test time equality in MySql

I have the next query:

UPDATE flights, airlines 
SET flights.business_booked_seats = flights.business_booked_seats + 3 
WHERE flights.airline_id = airlines.id 
AND airlines.name = EasyJet 
AND flights.origin_airport = London 
AND flights.destination_airport = Tokyo 
AND flights.departingDate = 2014-04-16 
AND flights.arrival_date = 2014-04-17 
AND flights.departing_time = 11:30:00 
AND flights.arrival_time = 04:00:00

and I get 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':30:00 AND flights.arrival_time = 04:00:00' at line 1

I also tried TIME(11:30:00) but it didn't work.

The structure of the tables is the following:

Airlines[id(int), name(varchar)]

Flights[origin_airport(varchar), destination_airport(varchar), departing_date(date), arrival_date(date), departing_time(time), arrival_time(time), economy_capacity(int), business_capacity(int), economy_booked_seats(int), business_booked_seats(int), economy_price(decimal), business_price(decimal), airline_id(int)]

How can I write the query correctly?

Upvotes: 1

Views: 200

Answers (2)

MPelletier
MPelletier

Reputation: 16657

You need to respect the SQL format.

All strings are between quotes (double or single), so you'll need:

AND airlines.name = 'EasyJet'
AND flights.origin_airport = 'London'
AND flights.destination_airport = 'Tokyo'

And you can convert strings into dates and times with STR_TO_DATE function:

AND flights.departingDate = STR_TO_DATE('2014-04-16','%Y-%m-%d')
AND flights.arrival_date = STR_TO_DATE('2014-04-17','%Y-%m-%d')

Or you can compare the strings:

AND flights.departingDate = '2014-04-16'
AND flights.arrival_date '2014-04-17'
AND flights.departing_time = '11:30:00'
AND flights.arrival_time = '04:00:00'

Addendum: STR_TO_DATE will be useful to you if you work with another format that is not YYYY-MM-DD. It works here without STR_TO_DATE because you were nice enough to provide the date in that format, but other formats (like the usual English MM/DD/YYYY won't match automatically).

Upvotes: 2

netopiax
netopiax

Reputation: 154

As suggested in a comment, using datetime types would probably make more sense. With that out of the way, you want to wrap your time literals in single quotes. '11:30:00'

TIME() won't do it - it takes an expression as a parameter, which 11:30:00 isn't, but TIME '11:30:00' would redundantly accomplish the same thing as just leaving it there in single quotes.

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

And since I can't comment due to reputation, I'll add here that using STR_TO_DATE is unnecessary.

Upvotes: 3

Related Questions