mysql查询时间周期,MySQL：周期日期范围从查询中的周号

I got a database table that looks something like this:

| id | clock | info

----------------------------------------------

| 1 | 1262556754 | some info

| 2 | 1262556230 | some other info

| 3 | 1262556988 | and another

| 4 | 1262555678 | and some more

It contains log records and a unix timestamp, when this log was written. What I need, is to get a weekly report, on how many log records there was during each week. Here is a query that I wrote:

SELECT

DATE_FORMAT(FROM_UNIXTIME(clock), "%U") AS week

count(*) as cnt

FROM logs

WHERE DATE_FORMAT(FROM_UNIXTIME(clock), "%Y") = '2010'

GROUP BY week

This gives a result like this:

| week | cnt

-------------------------------

| 1 | 55

| 2 | 134

| 4 | 765

| 20 | 65

Great! But what I would like to see, is a date ranges like 08 Feb 2010 00:00 - 15 Feb 2010 00:00, so my result set would look like this:

| day_start | day_end | cnt

---------------------------------------------------------

| 08 Feb 2010 00:00 | 15 Feb 2010 00:00 | 55

| 15 Feb 2010 00:00 | 22 Feb 2010 00:00 | 76

| 22 Feb 2010 00:00 | 01 Mar 2010 00:00 | 756

Is there any way to do this?

解决方案

Use STR_TO_DATE('201008 Monday', '%X%V %W'); to get a proper date like 2010-02-22 then use DATE_FORMAT to get the format you need.