Database living in the past?

Question

I have events in my MySQL database, each of which has a date. When I execute an SQL query to receive all events in the future, I get an error message ... Although the date of the events is in the future. When I modify my SQL query to select dates in the past, I get them from the future ...
The above SQL query worked before then, but for some reason it stopped working ...

I am using this SQL query:

$sql = "SELECT * FROM calendar WHERE date >= CURDATE() order by `date`"; 

I get an empty array as a result ...
However, if I change the request to this, I get all the events in my database:

 $sql = "SELECT * FROM calendar WHERE date <= CURDATE() order by `date`"; 

This is my data in the database. In my opinion, all the data in the future ...

The date table format is the default date type:

When I ask my server about the time echo date("Ymd"); I get today's date as a result ...

So where can I make a mistake?

Answer 1

Perhaps you are checking the wrong date field. Do you have a creation date as well as a planned date?

I might be crazy about the cold medicine I'm on right now, but your date table cannot be the date of your calendar items. The id is just an int (2), which seems small.

Answer 2

Perhaps something simpler? I noticed that the column name in your table is date , which is also the name of the date () function, which returns the date part of the datetime value. If this is the case

 $sql = "SELECT * FROM calendar c WHERE c.`date` <= CURDATE() order by `date`"; 

would do the trick. Even if not mysql itself, the gui application you are using (seems to be phpmyadmin for me) may get confused.

(By the way, you forgot the closing date tick in the order by clause)

Answer 3

Getting an empty set means that nothing was found. I would look at your formatting of your date. The only thing I thought was that it compares an unsurpassed type, so it just returns an empty set.

Answer 4

use DATEDIFF:

DATEDIFF

WHERE DATEDIFF (date, CURDATE)> 0

Answer 5

Before you execute your query, run this:

 SET time_zone = '-2:00'; // or whatever your time zone is. 

Answer 6

Don't ask me how and why, but I truncated the table and reinserted some data, and my query seems to work fine:

 $sql = "SELECT * FROM `calendar` WHERE `date` >= CURDATE() order by `date`"; 

So, even though the problems seem to be solved by truncating the table, I would like to know the answer to the question “why” ... Can someone provide me with this?