I've spent a couple of hours yesterday fixing where mydate>'2008-04-14 24:00' in a select query in a php script.

Only after I ran it in the normal mysql client, I saw that there was a warning about an incorrect date/time format.

So is it possible to retrieve MySql warnings using php ?

If you are using mysqli, then the mysqli_warning_count() function will tell you how many warning you have. You can then retrieve them using the SQL call SHOW WARNINGS. Check out the PHP manual for more details.

I don't use mysqli, but will have a look at an query with show warnings and see if I can use it with normal mysql.

Thanks a lot for the reply.

Hi

From php.ini (in the MySql section):

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Errors will be displayed.
mysql.trace_mode = Off

Not about the error message but about the error itself: 24:00 is not a valid time in MySQL. Although it should be, because it 2008-04-15 24:00 is the second after 2008-04-15 23:59.

After one full year developing a project which does a lot of date/time handling I found that it is generally better to do everything in Unix timestamps. That saves a lot of juggling between time/date and timestamps in PHP as well. Only for presentation one should use time/date, which is easy in PHP (gmtime and friends) and in MySQL any timestamp can be shown human readable as FROM_UNIXTIME(ts).

But that doesn't answer your original question, does it?

jlinkels

@jlinkels:
I expected the 24 to be the problem and running part of the query in the mysql client confirmed that. But it took me a while.

As I'm doing an update on an existing LAMP solution (that I wrote about 1.5 year ago) so it's a bit late to change the full design of my LAMP solution over to unix_timestamps. But I will definitely keep it in mind for the next application that I need to write.

@Guttorm:
thanks for that tip