RE: [PHP-DB] easier way to do this? (time interval)
| From: | John W. Holmes | Date: | Fri, 11 Oct 2002 14:47:27 +0000 |
| Subject: | RE: [PHP-DB] easier way to do this? (time interval) | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-22349@lists.php.net to get a copy of this message | ||
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: [PHP-DB] 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: [PHP-DB] 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