Epoch time and MySQL query

Question

I have a table like this:

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

whose date format is in the era. I have to group each row belonging to the same day and show them in a separate group. How can I do this in MySQL?

Thanks.

Answer 1

Group by:

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

Conclusion:

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

Sort by:

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

(Although in fact you would get the same order using only FROM_UNIXTIME () or the raw date value, since they would all flush correctly in an attempt to order)

Conclusion:

  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 to mysql datetime, and then extracts the date value from the one that applies to the grouping or order clause

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

However, could you tell us which group is “every day in a row”. what result do you want to show? when you group something, you use some kind of aggregate processor, such as COUNT () or SUM () in the field inside the group.

MySQL Group Function Reference

MySQL Date and Time Function Reference

Answer 2

Think about it, given the grouping requirement.

Just added MIN / MAX as an 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;