Question: The question sounds more complex than it probably is. I have a very simple table with php timestamps (just integers, not MySQL Date Fields) and float values. 

```
id | timestamp | values |

```

The values are stored in half hourly intervals, so each timestamp increments by half an hour.

I would like to aggregate a weeks worth of data into days in one query, so the response looks a something like this (1317596400 = OCT 3RD 2011)

```
0 | 1317596400 | 10.5
1 | 1317682800 | 9.65
2 | 1317769200 | 8.1
3 | 1317855600 | 9.6
4 | 1317942000 | 10.11
5 | 1318028400 | 12.1
6 | 1318114800 | 11.8

```

Where timestamp is 00:00 or 12:00, anything to identify the day. 

Any ideas?

UPDATE: 

I made some progress...

```
SELECT FROM_UNIXTIME(timestamp) as stamp, sum(data) FROM energy 
WHERE
timestamp >= 1317423600
AND
timestamp <= 1320105600
GROUP BY (  DATE( stamp ) ) 

```

but this seems to be limiting the response to a month. I don't seem to be able to query from the middle of 1 month to the the middle of another month.

Group by "Date" not "Day", otherwise you end up summing the values of (for example) May 1st and April 1st into one row.

Answer: 
I think the most difficult part is to parse the integer to a date in string. 

you could do it like this DATE( FROM\_UNIXTIME( 1317596400 ) )
You could convert the timestamp into a date, truncate the date to midnight and then convert it back into a timestamp value. A bit like this:

```
select unix_timestamp(date(from_unixtime(timestamp))) as "phpDate",
sum(someValues) as "values"
from myTable
group by phpDate;

```

Hope I've got the right end of the stick here!