Reputation: 47
I have an SQL query, which selects a datetime from a database. In another column called "add_minutes" are minutes I want to add to the datetime within the query. It should look like this:
SELECT * from availability WHERE ? < (datetime_start + add_minutes)
Any hints how to solve this?
Thank you!
Upvotes: 2
Views: 5711
Reputation: 416179
SELECT *
FROM availability
WHERE ? < DATE_ADD(datetime_start, INTERVAL add_minutes MINUTE)
Also:
SELECT *
FROM availability
WHERE ? < ADDTIME(datetime_start, SEC_TO_TIME(add_minutes * 60))
Note that MySql is dumb, and both DATE_ADD()
and ADDTIME()
work with string expressions. Because of potential localization/formatting issues, converting between numbers and strings can be surprisingly expensive operations, especially if you have to do this for every column in a table.
Additionally, what we're doing here breaks any possibility of using indexes you might have on these columns. You can improve performance considerably like this:
SELECT *
FROM availability
WHERE ADDTIME(?, SEC_TO_TIME(add_minutes * -60)) < datetime_start
This inverts the interval and adds it to the source value instead. It still needs to look at every value in the add_minutes
column, regardless of index, but now datetime_start
is unchanged, and therefore indexes on that column can still be used.
Just for fun, here's how Sql Server does it:
SELECT *
FROM availability
WHERE DATEADD(minute, add_minutes * -1, ?) < datetime_start
Sql Server is less dumb about it's DATEADD()
function. Everything here is numeric; there are no messy conversions between strings and numbers or dates. Sql Server also supports computed columns with indexes. So you could include an column in the table defined as DATEADD(minute, add_minutes, datetime_start)
, and have an index on that column. IIRC, MySql also supports computed columns, but does not support indexes on those columns.
Upvotes: 4