纪元时间和MySQL查询 [英] epoch time and MySQL query

问题描述

我有一个这样的表:

id   | date
---- | -----------
1    | 1319043263
2    | 1319043578

哪个日期字段格式为纪元.我必须将属于同一天的每一行分组，并在单独的分组中显示它们.如何在MySQL中做到这一点?

which date field format is in epoch. I have to group every row that belongs to same day and show them in a separate group. How can I do that in MySQL?

谢谢.

推荐答案

分组依据:

SELECT COUNT(`id`) AS `Records`, DATE(FROM_UNIXTIME(`date`)) AS `Date` 
FROM `table`
GROUP BY DATE(FROM_UNIXTIME(`date`))

输出:

    Records | Date
--------------------------------
      10    | 2011-10-19
      10    | 2011-10-18

订购者:

SELECT `id`, FROM_UNIXTIME(`date`) AS `Date`
FROM `table`
ORDER BY DATE(FROM_UNIXTIME(`date`)) [ASC|DESC]

(尽管实际上，您将仅使用FROM_UNIXTIME()或原始的date值即可获得相同的排序，因为它们都将在一次排序尝试中正确堆叠)

(Though in actuality you would get the same ordering using only FROM_UNIXTIME() or the raw date value since they would all stack properly in an ordering attempt)

输出:

      id    | Date
--------------------------------
      03    | 2011-10-19 12:00:00
      02    | 2011-10-18 12:00:00
      01    | 2011-10-17 12:00:00

这会将Unix时间戳转换为mysql日期时间，然后从应用于分组或订单子句的日期中提取日期值

This converts the unix timestamp into a mysql datetime and then extracts the date value from that which is applied to the grouping or order clause

如果您想按天分组，而不考虑月份和年份，请使用DAY()而不是DATE()

If you want to group by day regardless of month and year use DAY() instead of DATE()

但是，您可以扩展有关按天分组"的部分.您想显示什么结果?当您对某项进行分组时，您会在该组中的某个字段上使用某种聚合处理器，例如COUNT()或SUM().

However could you expand on the part about "group each row by day". what result do you want to show? when you group on something you use some sort of aggregate processor like COUNT() or SUM() on a field within the group.

MySQL组功能参考

MySQL Date&时间函数参考

这篇关于纪元时间和MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！