MySQL: week date range from week number in a query

Question

Hello!

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?

Answer 1

If that "week" column stores the number of a given week within a year, you could simply convert it to a date string using the makedate function.

E.g:

select makedate(2010, week * 7);

It should work, given that your reports are year-specific. Cheers

Answer 2

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.