RE: [PHP-DB] easier way to do this? (time interval)
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:[EMAIL PROTECTED]]
> Sent: Friday, October 11, 2002 7:25 AM
> To: [EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]]
> > Gesendet: Freitag, 11. Oktober 2002 06:34
> > An: [EMAIL PROTECTED]
> > 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 ->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
RE: [PHP-DB] easier way to do this? (time interval)
if(date("H")<7))
{
$var1 = 1;
$var2 = 0;
}
else
{
$var1 = 0;
$var2 = 1;
}
Then use this query:
SELECT dtg FROM techs WHERE tech='$user' AND (TO_DAYS(dtg) =
TO_DAYS(CURRENT_DATE) - $var1 DAY AND HOUR(dtg) > 7) AND (TO_DAYS(dtg) =
TO_DAYS(CURRENT_DATE) + $var2 DAY AND HOUR(dtg) < 7)
I _think_ that works, but don't have a table to test it on. Let me know
if it does. If it's before 7 am, it'll get all records between 7 am
yesterday and 7 am today. If it's after 7 am, it'll get all records
between 7 am today and 7 am tomorrow.
---John Holmes...
> -Original Message-
> From: Thoenen, Peter Mr. EPS
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 11, 2002 12:34 AM
> To: [EMAIL PROTECTED]
> Subject: [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 ->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
>
> ##
> Peter Thoenen - Systems Programmer
> Commercial Communications
> Camp Bondsteel, Kosovo
> ##
>
> "Stumbled Upon"...heh (Score:5, Funny) /.
> by $carab on 23:00 23 August 2002 (#4131637)
>
> "ForensicTec officials said they stumbled upon the military networks
about
> two months ago, while checking on network security for a
private-sector
> client."
>
> Someone new to a Dvorak probably tried to type in "lynx
> http://www.google.com"; but instead got "nmap -v -p 1-1024 -sS -P0
army.mil
> -T paranoid".
>
> --
> 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
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:[EMAIL PROTECTED]]
> Gesendet: Freitag, 11. Oktober 2002 06:34
> An: [EMAIL PROTECTED]
> 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 ->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-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 ->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
##
Peter Thoenen - Systems Programmer
Commercial Communications
Camp Bondsteel, Kosovo
##
"Stumbled Upon"...heh (Score:5, Funny) /.
by $carab on 23:00 23 August 2002 (#4131637)
"ForensicTec officials said they stumbled upon the military networks about
two months ago, while checking on network security for a private-sector
client."
Someone new to a Dvorak probably tried to type in "lynx
http://www.google.com"; but instead got "nmap -v -p 1-1024 -sS -P0 army.mil
-T paranoid".
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
