php count timestamp

[will35010]

I need some help. I have this script that pulls timestamps from the database. What I want to do is create a count of how many of those timestamps that were returned were from the morning(6am-12pm), Afternoon (12pm-6pm), Evening (6pm-12am), or Night (12am-6am). How do I do I parse the timestamps to do that and display the count. Thanks!

 

<?php

/**
* @author William Morris
* @copyright 2010
*/

include('inc/db.php');

//function to show date and time based on timestamp
function showTIME($timestamp){
    $date = date('F j Y h:i:s A', $timestamp);
    return $date;
}

$startdate = "2010-07-01";
$enddate = "2010-08-05";

//query to pull time stamps in between dates

//convert dates into timestamp
//$startdate = strtotime($_POST['startdate']);
//$enddate = strtotime($_POST['enddate']);

//convert dates into morning timestamp
$startdate1 = strtotime($startdate."06:00:00");
$enddate1 = strtotime($enddate."11:59:59");

$sql = mysqli_query($conn, "SELECT event_time FROM event WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate1' AND '$enddate1'");

while($row = mysqli_fetch_array($sql)){
    echo showTIME($row['event_time'])."<br>";
}
?>

[PFMaBiSmAd]

If you revisit your last thread about your code, you will find that your $enddate1 = strtotime($enddate."11:59:59"); ... calculations are not producing the value you think they are.

 

$enddate is the next day, so that is actually finding everything up to noon the next day, not the current day.

[systemick]

You don't say if the timestamps are unix timestamps. If they are and you wanted the number between 6AM and 12PM today the sql would be:

 

"select count(*) from event where event_time >= unix_timestamp('2010-08-06 06:00:00') and event time <= unix_timestamp('2010-08-06 12:00:00')

[will35010]

If you revisit your last thread about your code, you will find that your $enddate1 = strtotime($enddate."11:59:59"); ... calculations are not producing the value you think they are.

 

$enddate is the next day, so that is actually finding everything up to noon the next day, not the current day.

 

Thanks! I saw that as well. How do I parse the data to get a count of the timestamps in different times?

 

Updated Code:

<?php

/**
* @author William Morris
* @copyright 2010
*/

include('inc/db.php');

//function to show date and time based on timestamp
function showTIME($timestamp){
    $date = date('F j Y h:i:s A', $timestamp);
    return $date;
}

$startdate = strtotime("2010-07-01");
$enddate = strtotime("2010-08-05");

//query to pull time stamps in between dates

//convert dates into timestamp
//$startdate = strtotime($_POST['startdate']);
//$enddate = strtotime($_POST['enddate']);


$sql = mysqli_query($conn, "SELECT event_time FROM event WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate' AND '$enddate'");

while($row = mysqli_fetch_array($sql)){

        $time = date('F j Y h:i:s A', $row['event_time']);

}
?>

[will35010]

You don't say if the timestamps are unix timestamps. If they are and you wanted the number between 6AM and 12PM today the sql would be:

 

"select count(*) from event where event_time >= unix_timestamp('2010-08-06 06:00:00') and event time <= unix_timestamp('2010-08-06 12:00:00')

 

I was going to originally do it this way but the problem is the the date range is further than one day. And I need a separate count for each time period.

[PFMaBiSmAd]

Does the count for the 4 time periods accumulate for each day in the date range or is the count for the 4 time periods kept separate for each day in the date range?

[will35010]

Does the count for the 4 time periods accumulate for each day in the date range or is the count for the 4 time periods kept separate for each day in the date range?

 

Accumulate. Example ten events were morning over a ten day period and five were afternoon over the same ten day period.

[will35010]

Does the count for the 4 time periods accumulate for each day in the date range or is the count for the 4 time periods kept separate for each day in the date range?

 

Accumulate. Example ten events were morning over a ten day period and five were afternoon over the same ten day period.

 

The end goal is to get it to display something like this:

 

/**

echo "<table width='50%' border='0'>
  <tr>
    <td bgcolor='#d3d3d3'><div align='center'>Morning (6am-12pm)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Afternoon (12pm-6pm)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Evening (6pm-12am)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Night (12am-6am)</div></td>
  </tr>";

echo "<tr>
    <td><div align='center'>20</div></td>
    <td><div align='center'>10</div></td>
    <td><div align='center'>50</div></td>
    <td><div align='center'>5</div></td>
  </tr>";

echo "</table>";

**/

[PFMaBiSmAd]

If your data was stored as a mysql datetime or mysql timestamp (not to be confused with a Unix Timestamp), the following query does what you are after -

 

SELECT
sum(CASE WHEN TIME(date_time) BETWEEN '06:00:00' AND '11:59:59' THEN 1 END) AS morning ,
sum(CASE WHEN TIME(date_time) BETWEEN '12:00:00' AND '17:59:59' THEN 1 END) AS afternoon ,
sum(CASE WHEN TIME(date_time) BETWEEN '18:00:00' AND '23:59:59' THEN 1 END) AS evening ,
sum(CASE WHEN TIME(date_time) BETWEEN '00:00:00' AND '05:59:59' THEN 1 END) AS night
FROM your_table WHERE date_time BETWEEN '2010-07-01 00:00:00' AND '2010-08-05 23:59:59';

 

You could use the mysql FROM_UNIXTIME() function to get your Unix Timestamps onto DATETIME values in the query.

[will35010]

If your data was stored as a mysql datetime or mysql timestamp (not to be confused with a Unix Timestamp), the following query does what you are after -

 

SELECT
sum(CASE WHEN TIME(date_time) BETWEEN '06:00:00' AND '11:59:59' THEN 1 END) AS morning ,
sum(CASE WHEN TIME(date_time) BETWEEN '12:00:00' AND '17:59:59' THEN 1 END) AS afternoon ,
sum(CASE WHEN TIME(date_time) BETWEEN '18:00:00' AND '23:59:59' THEN 1 END) AS evening ,
sum(CASE WHEN TIME(date_time) BETWEEN '00:00:00' AND '05:59:59' THEN 1 END) AS night
FROM your_table WHERE date_time BETWEEN '2010-07-01 00:00:00' AND '2010-08-05 23:59:59';

 

You could use the mysql FROM_UNIXTIME() function to get your Unix Timestamps onto DATETIME values in the query.

 

I tried your query but it returns null in each field. I place the timestamp into the database by using time() so I think that's a unix timestamp.

[PFMaBiSmAd]

Someone already posted how to make the query that I posted work with a Unix Timestamp -

SELECT
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '06:00:00' AND '11:59:59' THEN 1 END) AS morning ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '12:00:00' AND '17:59:59' THEN 1 END) AS afternoon ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '18:00:00' AND '23:59:59' THEN 1 END) AS evening ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '00:00:00' AND '05:59:59' THEN 1 END) AS night
FROM your_table WHERE FROM_UNIXTIME(event_time) BETWEEN '2010-07-01 00:00:00' AND '2010-08-05 23:59:59';

[will35010]

Someone already posted how to make the query that I posted work with a Unix Timestamp -

SELECT
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '06:00:00' AND '11:59:59' THEN 1 END) AS morning ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '12:00:00' AND '17:59:59' THEN 1 END) AS afternoon ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '18:00:00' AND '23:59:59' THEN 1 END) AS evening ,
sum(CASE WHEN TIME(FROM_UNIXTIME(event_time)) BETWEEN '00:00:00' AND '05:59:59' THEN 1 END) AS night
FROM your_table WHERE FROM_UNIXTIME(event_time) BETWEEN '2010-07-01 00:00:00' AND '2010-08-05 23:59:59';

 

I tried this and it still returned nulls:

 

SELECT
sum(CASE WHEN TIME(event_time) BETWEEN '06:00:00' AND '11:59:59' THEN 1 END) AS morning ,
sum(CASE WHEN TIME(event_time) BETWEEN '12:00:00' AND '17:59:59' THEN 1 END) AS afternoon ,
sum(CASE WHEN TIME(event_time) BETWEEN '18:00:00' AND '23:59:59' THEN 1 END) AS evening ,
sum(CASE WHEN TIME(event_time) BETWEEN '00:00:00' AND '05:59:59' THEN 1 END) AS night
FROM event WHERE event = 'Registration' AND 
event_time >= unix_timestamp('2010-07-06') and event_time <= unix_timestamp('2010-08-06')

[will35010]

This SELECT * FROM `event` WHERE event = 'Registration'

 

produces:

 

eventid patientid visitid event         event_time username

21         0         3         Registration 1280759616 will

31         20    6         Registration 1280736699 will

22         18         4         Registration 1280760522 will

29         19         5         Registration 1280769567 will

[PFMaBiSmAd]

If you use the query I posted (adding event = 'Registration' AND to the WHERE clause), for the data you posted it returns 3 morning and 1 night result.

[will35010]

If you use the query I posted (adding event = 'Registration' AND to the WHERE clause), for the data you posted it returns 3 morning and 1 night result.

 

Thank you!