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]

 




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

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

  Powered by Linux