Think about it if there is only one entry in a single day, then the lowest date will still be the highest date, so you just need to select the max and the min dates and group by day select date_format(dateCreated, "%D %M %Y") as day, min(dateCreated) as lowest, max(dateCreated) as highest from yourTable group by day; On Wed, Mar 3, 2010 at 9:18 PM, Steve Obbayi <steve@sobbayi.com> wrote:
Hi Peter,
Based on your table assuming the dates are stored in a column called 'mydate' and table called 'datetable' then you need to run a query similar to this then am sure you can handle the presentation in PHP:
SELECT mydate AS given_day, EXTRACT(DAY FROM mydate) AS unique_days, MIN(mydate) AS earliest_time, MAX(mydate) AS latest_time FROM datetable GROUP BY unique_days;
if you want to extract just the time and ignore the days then use this one:
SELECT mydate AS given_day, TIME_FORMAT(EXTRACT(HOUR_SECOND FROM MIN(mydate)), '%H:%i:%s') AS earliest_time, TIME_FORMAT(EXTRACT(HOUR_SECOND FROM MAX(mydate)), '%H:%i:%s') AS latest_time, EXTRACT(DAY FROM mydate) AS unique_days FROM datetable GROUP BY unique_days;
Cheers ;-) Steve Obbayi
SKYPE: sobbayi http://sobbayi.com http://blog.sobbayi.com
----- Original Message ----- From: "Peter Karunyu" <pkarunyu@gmail.com> To: "Skunkworks forum" <skunkworks@lists.my.co.ke> Sent: Wednesday, March 3, 2010 10:43:18 AM GMT -05:00 US/Canada Eastern Subject: [Skunkworks] PHP/MySQL logic problem
Good evening skunks, I am retrieving the data below from a MySQL table, I want to display it on a page as follows: for any given day, I want to show the earliest time and the latest time, (for example, 2010-02-02 09:53:00 and 2010-02-02 10:50:00) on one row.
In cases where there is only one entry for a given day ( for example 2010-02-01 08:55:00), I will show that entry as both the earliest time and the latest time, still on one row
I am sure I can write more than one query which can use MIN() and MAX() and subqueries to retrieve the same, but I want to run the query once, retrieve all the data then apply logic in code to achieve the same.
I had written some PHP code which works only until it encounters single entries but its too messy to post here, someone please help?
2010-02-01 08:55:00 2010-02-02 09:53:00 2010-02-02 09:54:00 2010-02-02 10:48:00 2010-02-02 10:50:00 2010-02-04 16:55:00 2010-02-05 17:29:00 2010-02-16 09:35:00 2010-02-16 18:10:00 2010-02-17 10:09:00 2010-02-18 11:28:00 2010-02-18 14:58:00 2010-02-19 09:31:00 2010-02-19 12:28:00 2010-02-19 12:33:00 2010-02-19 16:58:00 2010-02-22 09:40:00 2010-02-22 15:26:00 2010-02-22 15:31:00 2010-02-23 11:52:00 2010-02-24 10:41:00 2010-02-24 18:32:00 2010-02-25 11:38:00 2010-02-25 18:08:00 2010-02-26 11:00:00 2010-02-26 18:20:00
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke Other lists ------------- Announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announceScience: http://lists.my.co.ke/cgi-bin/mailman/listinfo/science kazi: http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke Other lists ------------- Announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce Science: http://lists.my.co.ke/cgi-bin/mailman/listinfo/science kazi: http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general