Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
neesley
Forum Commoner
Posts: 26 Joined: Tue Aug 31, 2010 3:22 am
Post
by neesley » Thu Jul 14, 2011 10:29 pm
Here's a table I have with DATETIMEs in and out. I'd like to sum the TIMEDIFFs per p_ID and group them by p_ID.
Code: Select all
t_ID p_ID t_IN t_OUT
1 1 2011-07-13 18:54:56 2011-07-13 20:16:12
2 1 2011-07-14 09:26:56 2011-07-14 09:46:02
3 1 2011-07-14 10:06:39 2011-07-14 10:56:31
4 3 2011-07-14 13:07:04 2011-07-14 13:58:35
I've tried a few MySQL commands to no avail... My last attempt looked like this:
Code: Select all
SELECT p_ID, TIME_FORMAT(SUM(TIMEDIFF(t_OUT,t_IN)),'%H:%i') AS time FROM timeclock GROUP BY p_ID
I'm guessing my command is not summing per record per p_ID... Any help?
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Fri Jul 15, 2011 2:03 am
What do TIMEDIFF(t_OUT,t_IN) and SUM(TIMEDIFF(t_OUT,t_IN)) return? If you can get then nested functions to work, you may want to write the TIMEDIFFs to a temporary table and the do the SUM on that.
(#10850)
neesley
Forum Commoner
Posts: 26 Joined: Tue Aug 31, 2010 3:22 am
Post
by neesley » Sat Jul 16, 2011 7:36 pm
Thanks! I was hoping this would work but unfortunately not. Any problem with my syntax?
Code: Select all
$timeDiffReq = mysql_query("CREATE VIEW totals AS SELECT p_ID, TIMEDIFF(t_OUT,t_IN) AS time FROM timeclock");
$timeTotalReq = mysql_query("SELECT p_ID, SUM(time) FROM totals GROUP BY p_ID");
while($row = mysql_fetch_array($timeTotalReq)) {
echo "<div class=\"clockInOut\"><div id=\"name\">" . $row['p_ID'] . " - " . $row['time'] . "</div></div>";}
neesley
Forum Commoner
Posts: 26 Joined: Tue Aug 31, 2010 3:22 am
Post
by neesley » Sat Jul 16, 2011 8:01 pm
Nevermind! Success!
Code: Select all
$timeDiffReq = mysql_query("CREATE VIEW totals AS SELECT p_ID, TIME_TO_SEC(TIMEDIFF(t_OUT,t_IN)) AS time FROM timeclock");
$timeTotalReq = mysql_query("SELECT p_ID, SEC_TO_TIME(SUM(time)) AS timetotals FROM totals GROUP BY p_ID");
while($row = mysql_fetch_array($timeTotalReq)) {
echo "<div class=\"clockInOut\"><div id=\"name\">" . $row['p_ID'] . " - " . $row['timetotals'] . "</div></div>";}
$timeTotalDrop = mysql_query("DROP VIEW totals");