Timestamp (UNIX) and datetime function troubles.

[artech]

First of all:

MySQL: 5.1.46
PHP: 5.3.2

 

Next: Summary

Okay, so basically I have a dynamic graph that will allow the user to choose to view some data from the last 14 days, the last 14 weeks, and the last 12 months.  But, I'm having a hard time getting the min/max dates done correctly. I get the right number of records for each day in the last 14 days graph, but not in the other two graphs.

 

What I'm doing right now:

 

Getting the MIN and MAX UNIX timestamps for each of the last 14 days (by min/max I mean 00:00:01 and 23:59:59 for each day).

 

date_default_timezone_set('PDT'); 
// set current date
$current_date = strtotime(date('Y-m-d'));

// build an array for the last 14 days
for ($d = 13; $d >= 0; $d--) {
     $day_date[] = strtotime("-$d days", $current_date);
} // end days array

// get the UNIX timestamp for the first and last second of the day

foreach ($day_date as $key => $end) {

$current_date_new = date('Y-m-d', $end);

$current_date_max = $current_date_new." 23:59:59";
$current_date_min = $current_date_new." 00:00:01";

$current_date_maxUNIX = strtotime($current_date_max);
$current_date_minUNIX = strtotime($current_date_min);

// build the sql
$day_SQL = sprintf("SELECT count(complete = 'yes') as total 
                                         FROM " . $tableName  . " 
                                         WHERE timestampUNIX >= %s AND timestampUNIX <= %s", 
                                         $current_date_minUNIX, $current_date_maxUNIX);

        // query for the results
$data = DbRow($day_SQL, $database, $con); // !! this function just runs a standard query and returns the mysql_fetch_assoc array

        // format the date as we want to print it on the graph later
$datef = date('M-d', $end); 

        // add to the array
$day_data[] = array("day" => $end, "dayf" => $datef, "total" => $data['total']);

} // end foreach ($day_date)

 

I have attached a gif showing the graph with all records correctly counted/displayed (i have labeled it "14 DAYS").

 

 

Next, (and here's where this seems to start breaking down), I try to get the data for each of the last 14 weeks.  As a part of this I am trying to figure out the start and end max/min timestamps for the week.

 

// build an array for the last 14 weeks
for ($w = 13; $w >= 0; $w--) {
$week_date[] = strtotime("-$w weeks", $current_date);
} // end week array

// get the unix timestamp for the first and last second of the week
// $end represents the last day of each week

foreach ($week_date as $key => $end) {

// $end is the end of week date, so let's find the date of the beginning of the week
$endymdhis = date('Y-m-d H:i:s', $end); 

$start = strtotime("-6 days", $endymdhis); // this line gives me an error of "A non well formed numeric value encountered " when I have debugging on. I don't really know what that means but thought I should mention it.

// query for the data
$week_SQL = sprintf("SELECT count(complete = 'yes') as total 
                                            FROM " . $tableName . " 
                                            WHERE timestampUNIX >= %s 
                                            AND timestampUNIX <= %s", $start, $end);

// get the mysql_fetch_assoc array
$data = DbRow($week_SQL, $database, $con); // same function as described above returns assoc array

        // format date for display
$datef = date('M-d', $end);

        // add to the array
$week_data[] = array("week" => $end, "weekf" => $datef, "total" => $data['total']);

} // end foreach ($week_date)

 

This is not working properly. As shown in the attached gif labeled "14 WEEKS" it is only finding 9 records for this week and 2 for last week. All 59 records were entered in the last 2 weeks. 

 

Last, I try to grab the last 12 months and their first and last seconds in UNIX timestamps.

 

// build an array for the last 12 months
for ($m = 11; $m >= 0; $m--) {
$month_date[] = strtotime("-$m months", $current_date);
} // end date array: months

// monthly totals
foreach ($month_date as $end) {
// $end is really the end of month, so let's get the first of the month
$endymdhis = date('Y-m-d H:i:s', $end);
$start = strtotime("- 1 month", $endymdhis);// this line gives me an error of "A non well formed numeric value encountered " when I have debugging on. I don't really know what that means but thought I should mention it.
        $startymdhis = date('Y-m-d H:i:s', $start);
$start = strtotime("+ 1 day", $startymdhis);

// query for the data
$month_SQL = sprintf("SELECT count(complete = 'yes') as total 
                                             FROM " . $tableName . " 
                                             WHERE timestampUNIX >= %s 
                                             AND timestampUNIX <= %s", $start, $end);
$data = DbRow($month_SQL, $database, $con); // same function as before, returns assoc array

        // format the date as we'll print it out
$datef = date('M \'y', $end);

// add to the array
$month_data[] = array("month" => $end, "monthf" => $datef, "total" => $data['total']);

} // end foreach ($month_date)

 

A third attachment, labeled "12 MONTHS" shows that this is only seeing 7 records this month and none for last month, when all 59 records were entered this month or last.

 

Where have I gone wrong with my methods? I'm sure there are easier or less round-about ways to get the timestamps that I need. I just can't figure out what those easier ways are, LOL. In addition to the fact that I'm mostly likely making this harder than it needs to be, I'm not even getting the right data.

 

To sum this up, what I need is:

 

• UNIX timestamps for the first and last second (00:00:01 and 23:59:59) for each of the last 14 days
  
• UNIX timestamps for the first and last second for each of the last 14 weeks
  
• UNIX timestamps for the first and last second for each of the last 12 months
  

 

so that I can use these values to query the db for all records between those dates

 

THANKS SO MUCH!!!

 

[attachment deleted by admin]