Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Aug 2, 2009 at 8:02 PM, Govinda<govinda.webdnatalk@xxxxxxxxx> wrote:

You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date()
function in mysql on your timestamp column.


http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date

without knowing your original query it's hard to give an example, but:

select distinct date(timestamp_column) from table;

Thanks Chris,

I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to
grab data out of the 3 columns: solarLandingIP, solarLandingDir,
solarLandingDateTime  (this part of my SELECT is working).

This is what I have:

$foundTrackingRows=mysql_query("SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM ". $whichTable." ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
failed: " .mysql_error());

-Govinda

try

foundTrackingRows=mysql_query("SELECT DISTINCT
DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM ". $whichTable."
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
failed: " .mysql_error());


Bastien,

I just did try that. I also tried this, (thinking there was a typo in your version, in the DATE_FORMAT formatting string), :

$foundTrackingRows=mysql_query("SELECT DISTINCT DATE_FORMAT('%Y-%m- %d',solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable." ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed: " .mysql_error());

still no luck.

-Govinda



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux