Problems with strtotime

[geaclaesson]

Hi guys,

Used this forum a bit today to find answers for a bit of scripting that I'm attempting.
It's been 5-6 years since I last did anything like this, so bear with me.

My question is; why does this generate results from Dec 12 2011 and not from Dec 13 2011?

$date = "12/13/2011 00:01";
$form_init_date=strtotime($date);
$date2 = "12/13/2011 23:59";
$form_end_date=strtotime($date2);


$order = mysql_query("SELECT * FROM orders WHERE WORK='COMPLETED' AND fixed_date BETWEEN $form_init_date AND $form_end_date ")
or die(mysql_error());

[simplypixie]

What format are you expecting to get from your strtotime and what format are you using in your database for the date columns?

Also you original date format should be 13/12/2011 not 12/13/2011.

[Fest3er]

With '/', php assumes American; with '-', php assumes European. But that's prolly irrelevant, since mysql wants 'formatted' date/time, not seconds since 1/1/70.

Try

:
$date = "2011/12/13 00:00";
$date2 = "2011/12/13 23:59:59";


$order = mysql_query("SELECT * FROM orders WHERE WORK='COMPLETED' AND fixed_date BETWEEN $date AND $date2 ")
or die(mysql_error());

I think mysql wants date/time to be in a semi-human-readable format. (Hmmm. Semi-human readable? or semi human-readable? :)) Assuming, of course, that fixed_date is a date or datetime field. You might even try 24:00:00; mysql may be smart enough to deal with the intial moment of 'tomorrow' properly.

And to answer the specific question, I dunno. Mysql may assume some set date/time in the absence of a sane value.

FWIW, I've been referencing the online PHP manual at php.net and the MySQL reference PDF for years. Those two tomes have always gotten me through all problems, provided I pay attention to what the result or error message *says* and not the ASCII characters that are printed.

[geaclaesson]

Quote from the PHP manual:
"Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed."

This is why I wrote 12/13/2011 which in the American way of writing dates would be December 13th 2011.

I'll go ahead and try to add seconds on the dates as well. The problem is that I'm trying to script on somebody else's database and they've put the date in an integer form instead of mysql date.

Thank you guys so much for your answer, I'll go ahead and try to apply your suggestions now :)

[Fest3er]

Well then, it may depend on the version of PHP you are using or the version of the database software. What you wrote does correctly translate to 'seconds since 1/1/70' and back to the original using PHP5.3 on Debian Squeeze. The problem likely is somewhere else.

[geaclaesson]

Thank you for your input Fest3er. I'm going to troubleshoot the database and see if the date inserted in to the database is in another timezone or something.

[geaclaesson]

Ok, so I actually cracked the nut.. it seems that they were very thorough and changed the time stamp to be made in our timezone instead of the default UTC.

All that was needed was;

date_default_timezone_set('my timezone');

Again, a great big thanks for verifying the code.