MySQL date comparison

Question

Dates are stored in the database as such: "2011-05-26 11:00:00" in the datetime field. I would like to find all rows where the date is greater than the first of this month, that is: 2011-05-01 09:00:00

The date formats in the database cannot be changed. What MySQL function can I use to convert the date in the database to a format that the comparison can handle? I guess the best format for the "first month" is a unix timestamp?

Time values ​​are always present in the dates, but only hours of operation, therefore from 09:00 to 17:00.

Answer 1

If it is stored in the DATETIME field, just ask for WHERE date > '2011-05-01 09:00:00' .

Answer 2

 $firstDayOfMonth = date( 'Ymd H:i:s', mktime(0,0,0,date('n'),1,date('Y')); $query = "SELECT * FROM `table` WHERE `date` >= '$firstDayOfMonth'"; 

Answer 3

Is something like this possible?

 $oDateTime = new DateTime(); $sQuery = "SELECT * FROM table WHERE date >= ".$oDateTime->format("Ym")."-01 09:00:00"; 

Answer 4

Comparison with datetime directly:

 WHERE date_field >= '2011-05-01 00:00:00' 

Answer 5

You can use the TIMESTAMPDIFF function in MySQL to compare time,
The FROM_UNIXTIME function to format a Unix timestamp as a date.

Additional functions and examples of mysql data and time are available in the MySQL Date Manual Time and Date Function .

Answer 6

DATETIME fields can be filtered in exactly the same way as whole fields, for example:

 SELECT * FROM `table` WHERE `date` > '2011-05-01 09:00:00' 

If you really want to convert to Unix timestamps, take a look at UNIX_TIMESTAMP .

Answer 7

I think you can solve this, something like this:

firstly create the first day of the month in mysql format in php

 $firsDay = date('Ymd 00:00:00', strtotime(date('Y-m').'-01 00:00:00')); 

and then use it in the request

 select * from something where date >= $firsDay