Tricky Time Function for Reports

[jwwceo]

Hello,

 

My script records order date/time in UNIX timestamp. I want to write some reporting tools to show sales for every month since our store began. So basically, I need to write a function that will find the current month, say October 2008, and then all the previous months BEGINNING and END timestamps, going back to a start date. Then, I can use those BEGINNING and END timestamps in my mysql query to generate the reports.

 

Seems like a tricky endeavor, so I am wondering if there are any php tricks to make this easier.

 

James

[rhodesa]

mktime() and date() is what you want....the following will generate start/end timestamps for each month, starting from this month and ending with the month that $begin is in. $begin you should generate by selecting the MIN() timestamp from your table

 

<?php
  $begin = 1170493200; // 2/3/2007 03:00:00
  $min = mktime(0,0,0,date('m',$begin),1,date('Y',$begin));

  print '<table border=1><tr><th>Start Unix</th><th>Start Date</th><th>End Unix</th><th>End Date</th></tr>';
  for(
    $start = mktime(0,0,0,date('m'),1,date('Y')); //This month
    $start >= $min;
    $start = mktime(0,0,0,date('m',$start)-1,1,date('Y',$start)) //subtract a month
  ){
    $end = mktime(0,0,0,date('m',$start)+1,1,date('Y',$start)) - 1; //Next month minus 1 second
    printf('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',$start,date('r',$start),$end,date('r',$end));
  }
  print '</table>';
?>

[PFMaBiSmAd]

The problem with a Unix timestamp is that to do anything beyond comparisons, you must convert it to a usable format.

 

A database can produce most common reports, using sums, counts, min, max, ave... by year/month... This however requires that you EXTRACT the year/month information from the data, GROUP the data by the year/month, and the use the correct aggregate function to give you the results you need.

 

You can use the mysql FROM_UNIXTIME() to give you a DATETIME value from you Unix timestamp. You can use the mysql EXTRACT() function with a YEAR_MONTH parameter to give you year/month values that you can then do a GROUP BY on.

 

For example, if you wanted a sum of total sales by year/month -

 

SELECT EXTRACT(YEAR_MONTH,FROM_UNIXTIME(your_unix_timestamp_column)) as yearmonth,
SUM(sales) AS Total FROM your_table WHERE your_where_conditions_here GROUP BY yearmonth

[jwwceo]

Thanks. I'll try this stuff out.

[jwwceo]

I've got this query worked up...but its not working...can anyone see something wrong with this syntax?? Ive never used these extract functions before...might have a typo...

 

Im getting this error..

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM_UNIXTIME(date)) as yearmonth,
SUM(total) AS Total FROM x_cart_orders WHER' at line 1 

 

Query

 

SELECT EXTRACT(YEAR_MONTH FROM_UNIXTIME('date')) as yearmonth,
SUM(total) AS Total FROM x_cart_orders WHERE status = 'C' GROUP BY yearmonth

[PFMaBiSmAd]

You are missing a comma right after YEAR_MONTH,

[jwwceo]

Still not working, same error..

 

here's the exact query as I type it into phpMyAdmin

SELECT EXTRACT(YEAR_MONTH, FROM_UNIXTIME(date)) as yearmonth,
SUM(total) AS Total FROM xcart_orders WHERE status = 'C' GROUP BY yearmonth

 

 

[Barand]

SELECT EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date)) as yearmonth,

[PFMaBiSmAd]

Oops. Copy and paste error on my part in the original post. I copied the function name (to avoid spelling mistakes), but did not copy the rest of the syntax (couldn't they be consistent about parameter usage.) . P.S. looking up the function in the manual to see what it wanted there would have helped you solve this yourself.

[jwwceo]

Yes, I actually found another way to do it..

 

SELECT  FROM_UNIXTIME( xcart_orders.date,  '%M %Y' ) AS yearmonth, SUM( xcart_orders.total ) 

 

which is working,

 

Now of course I am on new problem, but I'll make a new post.

 

James