Search code examples
mysqlsqldatetimeinnodb

why can't I select all rows added with 30 minutes in MySQL

I have a dateAdded field that is a DATETIME type. I'm trying the query

SELECT * FROM table WHERE dateAdded > now() - interval 30 minute

It returns 0 rows even though there are a lot of rows created within 30 minutes. When I switch the greater than sign to a less than sign every row in the table is returned. I am using MySQL 5.6.28 and an InnoDB table. What reason could cause this not to work? I am doing a similar query on other tables with a DATETIME type and it works fine.

Solution

  • There seems to be a difference between the timezone used on the column (i.e. application inserting dateAdded data) and the timezone used on the client side issuing the query.

    SELECT NOW() - INTERVAL 30 MINUTE proved that difference.