Group timestamps and convert to years/months/weeks

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Group timestamps and convert to years/months/weeks

Post by JKM »

If I got a list of unix timestamps and I want to use google chart for some statistics. I don't know how to group by month/year etc by using the timestamps.

Code: Select all

if($_GET['w'] === 'overall') {
    // $x = |2004|2005|2006|2007|2008|2009 etc..
    // $y = some manually things
    // $v1 = <number of hits for 2004 (from db1)>, <number of hits for 2005 (from db1)> etc..
    // $v2 = <number of hits for 2004 (from db2)>, <number of hits for 2005 (from db2)> etc..
} elseif($_GET['w'] === 'year') {
    $year = $_GET['y'];
    // $x =  |January|February|Mars etc..  (only for the given month($year))
    // $y = some manually things
    // $v1 = <number of hits for January $year (from db1)>, <number of hits for February $year (from db1)> etc..
    // $v2 = <number of hits for January $year (from db2)>, <number of hits for February $year (from db2)> etc..
} elseif($_GET['w'] === 'month') {
    $month = $_GET['m'];
    // $x = |2004|2005|2006|2007|2008|2009 etc.. (only for the given month($month))
    // $y = some manually things
    // $v1 = <number of hits for 2004 $month (from db1)>, <number of hits for 2005 $month (from db1)> etc..
    // $v2 = <number of hits for 2004 $month (from db2)>, <number of hits for 2005 $month (from db2)> etc..
} else {
    echo 'Select something!';
}
?>
<img alt="" src="http://chart.apis.google.com/chart?chs=800x300&cht=lc&chxl=0:|<?php echo $x; ?>|1:|<?php echo $y; ?>&chd=t:<?php echo $v1; ?>|<?php echo $v2; ?>3&chds=0,99&chxr=0,0,99&chco=6090C0,008800&chg=14.28,16.66,1,5&chdl=<?php //blabla ?>&chxt=x,y" />
So, I would like to know how to find the following data:
- first year, last year
- list years and the number of rows per year
- first month, last month
- list months and the number of rows per months
.. And the same for weeks/days

Thanks for any help. :)
User avatar
akuji36
Forum Contributor
Posts: 190
Joined: Tue Oct 14, 2008 9:53 am
Location: Hartford, Connecticut

Re: Group timestamps and convert to years/months/weeks

Post by akuji36 »

Hello

Mysql has an interval function that may help:

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p9.php



also

php has a method that may help (using php offset--time)

http://www.youtube.com/user/phpacademy# ... 8OQMwhdI5M

thanks

Rod
Post Reply