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
> 
> 
> There is no comma between DATE(solarLandingDateTime) and  solarLandingIP
> which means the DATE column will use the alias 'solarLandingIP'. Is this
> your intention? Or is the  solarLandingIP another column from the table. 
> If the latter, you may want to do something like this:
> 
>  $foundTrackingRows=mysql_query("SELECT DISTINCT
> DATE(solarLandingDateTime) AS solarLandingDate,
> solarLandingIP, solarLandingDir, solarLandingDateTime
> FROM ".$whichTable.
> " ORDER BY solarLandingDateTime DESC
> LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());> 

Oops, forgot to mention that with the alias you can change the ORDER BY
clause to use the aliased column data:
ORDER BY solarLandingDate DESC
this will only use the returned data instead of the entire column.

> If you are aliasing a column it is better to use the optional AS keyword
> to avoid confusion.
> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so DATE_FORMAT
> is not needed here.

> --
> Niel Archer
> niel.archer (at) blueyonder.co.uk
> 
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--
Niel Archer
niel.archer (at) blueyonder.co.uk



-- 
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