epoch time and MySQL query

问题:

I have a table like this:

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

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?

Thanks.

回答1:

Group By:

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

Output:

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

Order By:

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

(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)

Output:

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

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

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

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 Group Functions Reference

MySQL Date & Time Function Reference

回答2:

Think it's rather like that, considering the requirement for grouping.

Just added MIN/MAX as example for domain aggregation.

SELECT
  DATE(FROM_UNIXTIME(`epoch`)) AS `GroupedDate`,
  MIN(FROM_UNIXTIME(`epoch`)) AS `DayStart`,
  MAX(FROM_UNIXTIME(`epoch`)) AS `DayEnd`
FROM
  `timestamps`
GROUP BY
  DATE(FROM_UNIXTIME(`epoch`))
ORDER BY
   `epoch` ASC;