通过Timestamp条目聚合和可视化SQLite数据库中的条目

Question

I have a big SQLite - Database that contains entries like this:

rowID   timestamp    employee  sold         
1       1345294648   myer      1
2       1351508109   miller    2          
3       1351508109   smith     8
4       1351508109   miller    10
5       1351508109   smith     1
6       1353243448   myer      10

It represents the amount of units certain employees have sold at a certain time.

With the following code in php i can get the sold units per employee:

<?php

$response = array();
$db = new PDO("sqlite:./db.sqlite");

$query_count = $db -> query("SELECT * FROM myTable GROUP BY employee");

while ($row = $query_count -> fetch(PDO::FETCH_ASSOC)) {

$current_employee = $row['employee'];

$response[$current_employee]["sold"] = $row['sold'];

}

echo json_encode($response);

?>

This will give back the following JSON - formatted data:

{ 
  "miller" : { "sold" : 12 },
  "myer" : { "sold" : 11 },
  "smith" : { "sold" : 9 }
}

Now i want to separate the sales to the distinct months, like this:

{
   "miller":{
      "sold":{
         "january":1,
         "february":8,
         "march":9
      }
   },
   "myer":{
      "sold":{
         "january":10,
         "february":11,
         "march":13
      }
   },
   "smith":{
      "sold":{
         "january":11,
         "february":10,
         "march":10
      }
   }
}

After that, i want to visualize the data with an bar chart, like this but non-stacked:

http://bl.ocks.org/1134768

Is there a SQL - Request / PHP-Script that can help me?

I'm glad for every answer, thanks in advance.

Answer 1

SQLite delivers different date functions, to aggregate data by dates:

http://www.sqlite.org/lang_datefunc.html