带时间戳的数据库，显示不同的月份

I have a table that saves my upload / download statistics per 15 minutes.

我有一个表，每15分钟保存一次上传/下载统计信息。

This is what the table looks like

这就是表格的样子

| ID  | timestamp  | upload | download | packetssent | packetsreceived 
| 1   | 1328828401 | 343245 | 93473463 | 4343423     | 3435664
| 2   | 1328829301 | 343245 | 93473463 | 4343423     | 3435664

What i want is when you come first on the page you see total download/upload. And then a list of the months available. (So currently is that only February), Once you click on a month, it wil show the day's and their totals, when you click on a day, you will see all records for that single day

我想要的是当你第一次在页面上看到总下载/上传时。然后是可用月份列表。（所以目前只有二月份），一旦你点击一个月，就会显示当天和他们的总数，当你点击一天时，你会看到那一天的所有记录

I need advice how to do this.

我需要建议如何做到这一点。

Thanks in advance

提前致谢

3 个解决方案

#1

Try the following (for the current year):

尝试以下（当年）：

SELECT 
   FROM_UNIXTIME(timestamp, "%m") AS m, 
   FROM_UNIXTIME(timestamp, "%Y") AS Y, 
   SUM(packetssent ) AS sent, 
   SUM(packetsreceived) AS received
FROM atable
GROUP BY Y, m HAVING Y=YEAR(NOW())

#2

To extract the various values, use the date/time functions in MySQL

要提取各种值，请使用MySQL中的日期/时间函数

DAY(from_unixtime(timestamp))
MONTH(from_unixtime(timestamp))
YEAR(from_unixtime(timestamp))

HOUR(FROM_UNIXTIME(timestamp))
MINUTE(FROM_UNIXTIME(timestamp))
SECOND(FROM_UNIXTIME(timestamp))

You can use these in group by sections to extract your information dependent on a variety of time frames

您可以逐个部分使用这些来根据各种时间范围提取信息

Have a look at: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html for a full list

请查看：http：//dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html获取完整列表

#3

I think you can use

我想你可以用

SELECT DISTINCT MONTH(DATE(timestamp)) FROM Table.

You might get away with

你可能会侥幸逃脱

SELECT DISTINCT MONTH(timestamp) FROM table.

but if you can the documentation for the MONTH() function doesn't state that you can pass a timestamp to it.

但是，如果可以，MONTH（）函数的文档不会声明您可以向其传递时间戳。

#1