Home > Archive > PHP DB > March 2004 > Re: [PHP-DB] DATE_SUB Issues
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] DATE_SUB Issues
|
|
| John W. Holmes 2004-03-29, 10:32 am |
| From: "Craig Hoffman" <choffman@katomic.com>
> Perhaps someone could lend me a hand here on this query. I have a
> query where I would like it to SUM up the last 7 days of records
> further, It needs to start a new w on Monday. The 'time_upload'
> field is a datetime field. What am I doing wrong or not doing here ?
>
> SELECT SUM(distance), DATE_FORMAT('time_upload', '%u'),
> WEEK('time_upload', '7') FROM TRAININGLOG WHERE DATE_SUB(NOW(),INTERVAL
> 7 DAY )
> < =time_upload
If time_upload is a column, it should not be between single quotes in the
WEEK() function.
---John Holmes...
| |
| Craig Hoffman 2004-03-29, 10:33 am |
| still no luck - any other suggestions?
__________________________________
Craig Hoffman - eClimb Media
v: (847) 644 - 8914
f: (847) 866 - 1946
e: choffman@eclimb.net
w: www.eclimb.net
_________________________________
On Mar 29, 2004, at 8:18 AM, John W. Holmes wrote:
> From: "Craig Hoffman" <choffman@katomic.com>
>
>
> If time_upload is a column, it should not be between single quotes in
> the
> WEEK() function.
>
> ---John Holmes...
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
| John W. Holmes 2004-03-29, 12:31 pm |
| From: "Craig Hoffman" <choffman@katomic.com>
> still no luck - any other suggestions?
Please define "no luck"
---John Holmes...
[color=darkred]
> On Mar 29, 2004, at 8:18 AM, John W. Holmes wrote:
| |
|
|
| Craig Hoffman 2004-03-29, 4:31 pm |
| WooHoo! I got it working!
Basically what I did is I changed this DATE_SUB( NOW(),INTERVAL 7 DAY
) to DATE_SUB( NOW(),INTERVAL 1 DAY ) and it worked. I am not
entirely sure why. Anyone know?
__________________________________
Craig Hoffman - eClimb Media
v: (847) 644 - 8914
f: (847) 866 - 1946
e: choffman@eclimb.net
w: www.eclimb.net
_________________________________
On Mar 29, 2004, at 10:56 AM, Craig Hoffman wrote:
> What is should doing is grabbing all entries of this w and return a
> total. Its returning the wrong results.
>
> SELECT SUM(distance), DATE_FORMAT(time_upload, '%u'),
> WEEK(time_upload, 1) FROM TRAININGLOG WHERE DATE_SUB( NOW(),INTERVAL 7
> DAY ) <= time_upload GROUP BY time_upload LIMIT 1
>
> => Results: (This is wrong)
> => Wly Miles: (W starts on Monday) 5.34
>
> Correct Results should be this:
> Wly Miles: 30.5
> __________________________________
> Craig Hoffman - eClimb Media
>
> v: (847) 644 - 8914
> f: (847) 866 - 1946
> e: choffman@eclimb.net
> w: www.eclimb.net
> _________________________________
> On Mar 29, 2004, at 10:29 AM, John W. Holmes wrote:
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
| |
| Ricardo Lopes 2004-03-30, 6:30 am |
| the first time i saw this post i get  , let me see if i understand.
you want the _CURRENT_ w stats
OR
the stats from the last 7 days
if you want the first you cant use now
the reason why that script works was because it was monday :)
> SELECT SUM(distance), DATE_FORMAT(time_upload, '%u'),
----- Original Message -----
From: "Craig Hoffman" <choffman@katomic.com>
To: <php-db@lists.php.net>
Sent: Monday, March 29, 2004 9:48 PM
Subject: Re: [PHP-DB] DATE_SUB Issues
[color=darkred]
> WooHoo! I got it working!
> Basically what I did is I changed this DATE_SUB( NOW(),INTERVAL 7 DAY
> ) to DATE_SUB( NOW(),INTERVAL 1 DAY ) and it worked. I am not
> entirely sure why. Anyone know?
> __________________________________
> Craig Hoffman - eClimb Media
>
> v: (847) 644 - 8914
> f: (847) 866 - 1946
> e: choffman@eclimb.net
> w: www.eclimb.net
> _________________________________
> On Mar 29, 2004, at 10:56 AM, Craig Hoffman wrote:
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
| Ricardo Lopes 2004-03-30, 6:30 am |
| sorry, i press Control+Enter and i send the unfinished mail, here is the
continuation:
it worked because it was monday, probably today you arent happy.
try this sql, but i dont like the GROUP clause
SELECT SUM(distance), DATE_FORMAT(time_upload, '%u'), WEEK(time_upload, 1)
FROM TRAININGLOG
WHERE (WEEK(NOW(), 1) <= WEEK(time_upload, 1))
GROUP BY time_upload
LIMIT 1
----- Original Message -----
From: "Craig Hoffman" <choffman@katomic.com>
To: <php-db@lists.php.net>
Sent: Monday, March 29, 2004 9:48 PM
Subject: Re: [PHP-DB] DATE_SUB Issues
> WooHoo! I got it working!
> Basically what I did is I changed this DATE_SUB( NOW(),INTERVAL 7 DAY
> ) to DATE_SUB( NOW(),INTERVAL 1 DAY ) and it worked. I am not
> entirely sure why. Anyone know?
> __________________________________
> Craig Hoffman - eClimb Media
>
> v: (847) 644 - 8914
> f: (847) 866 - 1946
> e: choffman@eclimb.net
> w: www.eclimb.net
> _________________________________
> On Mar 29, 2004, at 10:56 AM, Craig Hoffman wrote:
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
| Craig Hoffman 2004-03-30, 9:35 am |
| Thanks - I'll play with it today.
__________________________________
Craig Hoffman - eClimb Media
v: (847) 644 - 8914
f: (847) 866 - 1946
e: choffman@eclimb.net
w: www.eclimb.net
_________________________________
On Mar 30, 2004, at 3:32 AM, Ricardo Lopes wrote:
> sorry, i press Control+Enter and i send the unfinished mail, here is
> the
> continuation:
>
> it worked because it was monday, probably today you arent happy.
>
> try this sql, but i dont like the GROUP clause
>
> SELECT SUM(distance), DATE_FORMAT(time_upload, '%u'),
> WEEK(time_upload, 1)
> FROM TRAININGLOG
> WHERE (WEEK(NOW(), 1) <= WEEK(time_upload, 1))
> GROUP BY time_upload
> LIMIT 1
>
> ----- Original Message -----
> From: "Craig Hoffman" <choffman@katomic.com>
> To: <php-db@lists.php.net>
> Sent: Monday, March 29, 2004 9:48 PM
> Subject: Re: [PHP-DB] DATE_SUB Issues
>
>
>
| |
| Craig Hoffman 2004-03-30, 9:35 am |
| sorry about the multiple reply's. I tested the query with a few future
dates and it seemed to be worked. Thanks again for your help.
Thanks - Craig
__________________________________
Craig Hoffman - eClimb Media
v: (847) 644 - 8914
f: (847) 866 - 1946
e: choffman@eclimb.net
w: www.eclimb.net
_________________________________
On Mar 30, 2004, at 3:32 AM, Ricardo Lopes wrote:
> sorry, i press Control+Enter and i send the unfinished mail, here is
> the
> continuation:
>
> it worked because it was monday, probably today you arent happy.
>
> try this sql, but i dont like the GROUP clause
>
> SELECT SUM(distance), DATE_FORMAT(time_upload, '%u'),
> WEEK(time_upload, 1)
> FROM TRAININGLOG
> WHERE (WEEK(NOW(), 1) <= WEEK(time_upload, 1))
> GROUP BY time_upload
> LIMIT 1
>
> ----- Original Message -----
> From: "Craig Hoffman" <choffman@katomic.com>
> To: <php-db@lists.php.net>
> Sent: Monday, March 29, 2004 9:48 PM
> Subject: Re: [PHP-DB] DATE_SUB Issues
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
|
|
|
|
|