Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Select
cast(`Datetime` as date),
timediff(Max(`datetime`), min(`datetime`))
From timesheet
Group by cast(`datetime` as date) asc
<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=8889;dbname=datetest', 'root','******');
$pdo->exec('drop table dbtest');
$pdo->exec("create table dbtest (ts timestamp, t enum('start','stop'))");
$pdo->exec('SET @@session.time_zone = "+00:00"');
date_default_timezone_set('UTC');
$now = strtotime('2014-03-23 09:00');
$i = $pdo->prepare('insert into dbtest(ts, t) values (from_unixtime(?), ?)');
$timePeriods = array(0.5, 1, 1.5, 2, 2.5, 3);
$diffPeriods = array(15, 30, 45, 60);
for($ii=1; $ii<=200;$ii++):
for($j = 0; $j<3; $j++):
$tpRand = rand(0, count($timePeriods) - 1);
$dpRand = rand(0, count($diffPeriods) - 1);
if($j == 0):
$curTime = strtotime("-$ii day", $now);
else:
$curTime = strtotime("+" . $diffPeriods[$dpRand] . " minute", $curTime);
endif;
$result = $i->execute(array($curTime, 'start')); //enter the start time
$curTime = strtotime("+" . (60 * $timePeriods[$tpRand]) . " minute", $curTime);
$i->execute( array( $curTime,'stop' ) );
endfor;
endfor;
SELECT
CAST(ts AS DATE) as `day`,
SEC_TO_TIME(
SUM(
TIME_TO_SEC(
TIME(
CASE t
WHEN 'stop'
THEN ts
ELSE 0
END
)
)
) -
SUM(
TIME_TO_SEC(
TIME(
CASE t
WHEN 'start'
THEN ts
ELSE 0
END
)
)
)
) AS timespend
FROM dbtest
GROUP BY CAST(ts AS DATE) DESC
day timespend
2014-03-22 05:30:00
2014-03-21 07:00:00
2014-03-20 02:30:00
2014-03-19 06:00:00
2014-03-18 07:00:00
2014-03-17 03:30:00
2014-03-16 02:30:00
day timespend
2014-03-22 5.5000
2014-03-21 7.0000
2014-03-20 2.5000
2014-03-19 6.0000
2014-03-18 7.0000
2014-03-17 3.5000
SELECT
CAST(ts AS DATE) as `day`,
userID,
SEC_TO_TIME(
SUM(
TIME_TO_SEC(
TIME(
CASE t
WHEN 'stop'
THEN ts
ELSE 0
END
)
)
) -
SUM(
TIME_TO_SEC(
TIME(
CASE t
WHEN 'start'
THEN ts
ELSE 0
END
)
)
)
) AS timespend
FROM dbtest
WHERE YEAR(NOW()) = YEAR(ts) AND MONTH(NOW()) = MONTH(ts)
GROUP BY CAST(ts AS DATE) DESC, userID ASC
HAVING userID IN (1,10,15)
day userID timespend
2014-03-22 1 05:30:00
2014-03-22 10 04:00:00
2014-03-22 15 05:30:00
2014-03-21 1 04:30:00
2014-03-21 10 04:30:00
2014-03-21 15 05:30:00
2014-03-20 1 05:30:00
2014-03-20 10 02:30:00
2014-03-20 15 05:00:00
2014-03-19 1 05:30:00
2014-03-19 10 07:00:00
2014-03-19 15 04:30:00
2014-03-18 1 08:00:00
2014-03-18 10 04:00:00
2014-03-18 15 01:30:00
2014-03-17 1 07:00:00
2014-03-17 10 03:30:00
2014-03-17 15 06:30:00
2014-03-16 1 02:00:00
2014-03-16 10 06:30:00
2014-03-16 15 07:30:00
2014-03-15 1 06:30:00
2014-03-15 10 04:30:00
2014-03-15 15 05:30:00
2014-03-14 1 06:30:00
2014-03-14 10 06:00:00
2014-03-14 15 04:30:00
2014-03-13 1 05:00:00
2014-03-13 10 03:30:00
2014-03-13 15 06:30:00
2014-03-12 1 07:00:00
2014-03-12 10 05:30:00
2014-03-12 15 03:00:00
2014-03-11 1 04:30:00
2014-03-11 10 06:00:00
2014-03-11 15 03:30:00
2014-03-10 1 08:00:00
2014-03-10 10 07:00:00
2014-03-10 15 05:00:00
2014-03-09 1 02:00:00
2014-03-09 10 03:30:00
2014-03-09 15 03:30:00
2014-03-08 1 01:30:00
2014-03-08 10 06:00:00
2014-03-08 15 04:30:00
2014-03-07 1 03:30:00
2014-03-07 10 05:30:00
2014-03-07 15 06:00:00
2014-03-06 1 06:30:00
2014-03-06 10 05:30:00
2014-03-06 15 04:30:00
2014-03-05 1 06:30:00
2014-03-05 10 03:30:00
2014-03-05 15 04:30:00
2014-03-04 1 05:30:00
2014-03-04 10 08:30:00
2014-03-04 15 06:00:00
2014-03-03 1 04:30:00
2014-03-03 10 03:30:00
2014-03-03 15 04:30:00
2014-03-02 1 02:30:00
2014-03-02 10 05:30:00
2014-03-02 15 06:00:00
2014-03-01 1 08:30:00
2014-03-01 10 03:30:00
2014-03-01 15 06:30:00