I have a mySQL table that stores the high temperatures for every day over a span of several years. When A given day's temp is logged to the database, a UNIX timestamp for midnight GMT time for that day is logged as well. I need to run a query that will return all the temps over the years and the date they're associated with for any user-selected day and month. So for example, if the use selects August 11th, I need the high temperature for 8/11/2003, 8/11/2004, 8/11/2005, etc (1060560000, 1092182400, 1123718400, respectively)... all records with a timestamp that will convert to that date.
I know I can generate the exact timestamps for that date for the years the data spans and use them in the query like this:
SELECT tStamp, high_temp FROM temps WHERE tStamp=1060560000 OR tStamp=1092182400 OR tStamp=1123718400;
but this obviously would not be ideal. In order to allow for an unlimited range of years, I'll need something that can find multiples or something to that effect... I think?
I tried (in PHP):
$in = $_POST['date']; // ex: 8/11
$mult = strtotime($in . '/1970 00:00:00');
$sql = "SELECT tStamp, high_temp FROM temps WHERE MOD(tStamp, $mult) = 0";
....
but this did not return any records for August 11th. I'm not sure where to go from here. Any insight would be greatly appreciated.
