I don't think this would work. You're asking for rows where the date is greater than 24 hours ago, and the hour is greater than seven. So, if it's 8 am in the morning, you'll get results from 8 am yesterday until midnight, then from 7 am today until 8 am today. You'll miss the rows from midnight until 7 am, right? Or is my logic wrong? ---John Holmes... > -----Original Message----- > From: Thomas Lamy [mailto:Thomas.Lamy@netwake.de] > Sent: Friday, October 11, 2002 7:25 AM > To: php-db@lists.php.net > Subject: Re: easier way to do this? (time interval) > > Hi, > > convert your date to UNIX_TIMESTAMP, which is number of seconds since > 1.1.1970, and you can use simple math, as in > > $query=mysql_query(" > SELECT dtg > FROM techs > WHERE > tech='$user' > AND > UNIX_TIMESTAMP(dtg) > UNIX_TIMESTAMP(NOW()) - 86400 > AND > HOUR(dtg)>=7 > "); > ... where 86400 = 24 hours (in seconds) > > See <http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1302> > > > Thomas > > > > -----Ursprüngliche Nachricht----- > > Von: Thoenen, Peter Mr. EPS > > [mailto:Peter.Thoenen@bondsteel2.areur.army.mil] > > Gesendet: Freitag, 11. Oktober 2002 06:34 > > An: php-db@lists.php.net > > Betreff: easier way to do this? (time interval) > > > > > > Hello, > > > > Curious if there is an easier way to do this (using just SQL > > and not PHP). > > SQL seems powerful enough to do this but can't think of the > > synatx. Note, I > > am using MySQL so no sub-selects (or other useful items). > > Basically trying > > to pull all records for a 24 hour period but instead of 0000->2400, > > 0700->0700 (next day). > > > > if (date("H")>7){ > > > > $query=mysql_query(" > > SELECT dtg > > FROM techs > > WHERE > > tech='$user' > > AND > > DAYOFMONTH(NOW())=DAYOFMONTH(dtg) > > AND > > HOUR(dtg)>=7 > > "); > > > > } else { > > > > $query=mysql_query(" > > SELECT dtg > > FROM techs > > WHERE > > tech='$user' > > AND > > ( > > (DAYOFMONTH(NOW())=DAYOFMONTH(dtg) AND HOUR(dtg)<7) > > OR > > ((DAYOFMONTH(NOW())-1)=DAYOFMONTH(dtg) AND HOUR(dtg)>=7) > > ) > > "); > > > > } > > > > Cheers, > > > > -peter > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
