CODEWITHSUNDEEP
mysqlsql
Arno
Arno

Reputation: 47

Add minutes to datetime in SQL SELECT from other column

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

Answers (1)

Joel Coehoorn
Joel Coehoorn

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

Related Questions