mysql时间戳group by操作,GROUP BY间隔重叠时间戳MySQL查询

I've been reading other questions and had no luck. I have a table with columns (stamp,value). The 'stamp' column stores hourly timestamps. What I'm trying to do is to retrieve SUM(value) for intervals of 24 hours depending on a range of timestamps specified in the query:

SELECT stamp,

Sum(value)

FROM table

WHERE stamp >= Date_sub('2012-12-02 05:00:00', INTERVAL 1 day)

AND stamp < '2013-01-01 05:00:00'

GROUP BY Date(stamp)

The result I'm looking for would look like this:

stamp value

2012-12-02 05:00:00 12024

2012-12-03 05:00:00 11211

2012-12-04 05:00:00 19834

...

2013-01-01 05:00:00 10232

Actual table looks like this:

stamp value

2012-12-01 01:00:00 345

2012-12-01 02:00:00 100

2012-12-01 03:00:00 104

2012-12-01 04:00:00 103

2012-12-01 05:00:00 101

2012-12-01 06:00:00 102

...

2013-01-01 05:00:00 207

2013-01-01 06:00:00 307

2013-01-01 07:00:00 223

...

*Note that the stamp column's datatype is timestamp

解决方案

I think you just want to set the hours to 5, to be consistent with your range. If so, you can do this:

SELECT cast(date(stamp) as datetime) + interval 5 hour

Sum(value)

FROM table

WHERE stamp >= Date_sub('2012-12-02 05:00:00', INTERVAL 1 day)

AND stamp < '2013-01-01 05:00:00'

GROUP BY Date(stamp)

But, I think you might actually want to phrase this a bit differently:

SELECT cast(date(stamp - interval 5 hour) as datetime) + interval 5 hour

Sum(value)

FROM table

WHERE stamp >= Date_sub('2012-12-02 05:00:00', INTERVAL 1 day)

AND stamp < '2013-01-01 05:00:00'

GROUP BY Date(stamp - interval 5 hour)

This actually realigns the dates on the 5-hour boundary.

Note: these queries are untested, so may contain syntax errors.

This is assuming that the timestamp is really a datetime. If it is a UNIX timestamp, you need to convert it first.