CODEWITHSUNDEEP
mysqlsql
Forest
Forest

Reputation: 938

Selecting rows with timestamps set to the future

I have a table of adverts. These adverts have start and end columns, which are both of the DATETIME type.

I need to select ones that are going to start in the next 24 hours, and, separately, ones that are going to end in the next 24 hours. I wrote the query select * from slides where enabled = 1 and start <= NOW() + interval 24 hour, which seemed to work at first.

The problem is, it also selects rows from a long time ago. I need it just to select ones starting between now and 24 hours from now.

What am I doing wrong?

Upvotes: 0

Views: 565

Answers (3)

Ubercool
Ubercool

Reputation: 1021

This works in oracle you can modify it according the DB you are using

select * from slides where enabled = 1 and start between sysdate and sysdate+1

Here sysdate returns current date and time, adding 1 to it returns date and time 24 hours from now.

Upvotes: 2

EastOfJupiter
EastOfJupiter

Reputation: 781

I have a SQL Server background, but did a little research into the particularities of MySQL.

I need to select ones that are going to start in the next 24 hours

/*This will provide you all that start in next 24 hours*/
SELECT S.* FROM Slides S WHERE S.Enabled = 1 AND S.Start <= NOW() + interval 24 hour AND
  S.Start >= NOW();

and, separately, ones that are going to end in the next 24 hours

        /* This will separately select the ones that are going to end in the next 24 */
    hours.
UNION 
        SELECT S.* FROM Slides S WHERE S.Enabled = 1 AND S.END <= NOW() + interval 24 hour AND
  S.End>= NOW();

Run the two code blocks together as a single statement. You will end up with a single result set that is comprised of two separate results from two differing statements.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

So, use two comparisons:

select s.*
from slides s
where s.enabled = 1 and
      s.start <= NOW() + interval 24 hour and
      s.start >= NOW();

Upvotes: 1

Related Questions