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-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en ------------ 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

_______________________________________________
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-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
------------
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