> The timestamp column is a varchar.
Is that because you are using a MySQL version that did not support fractional seconds? Consider upgrading to the latest 5.6 version, which does. In the following solution I'll assume MySQL 5.6 and a real timestamp column.
The requirement presents two problems:
(1) How to identify values as belonging within the desired intervals:
Your summing period begins at 1600 hours on a given date, and ends at 15:59:59.999 on the next day, so for any given day, the time boundaries are ...
concat( date(timestamp), ' 16:00:00:000' ) [begin]
concat( date(timestamp)+interval 1 day, ' 15:59:59:999' ) [end]
For optimal performance, write a simple query that creates such a "calendar" table so you can efficiently join from it to your data table, eg ...
drop table if exists cal;
create table cal
select distinct
concat( date(timestamp), ' 16:00:00:000' ) as tbegin,
concat(date(timestamp)+interval 1 day, ' 15:59:59:999' ) as tend
from t;
In what follows I'll derive the `cal` table dynamically.
(2) How to sum the values for those periods:
Once we have a SQL representation of the summing periods, ie as the columns cal.tbegin and cal.tend, all we need do is join from the calendar to the data table on timestamp between cal.tbegin and cal.tend, and Group By tbegin, tend.
In this solution I'll assume your table is named `t`. Here is the whole query:
select cal.tbegin, cal.tend, sum(vals.value1), sum(vals.value2)
from (
select distinct
concat( date(timestamp), ' 16:00:00:000' ) as tbegin,
concat(date(timestamp)+interval 1 day, ' 15:59:59:999' ) as tend
from t
) as cal
join (
select timestamp,value1,value2
from t
) as vals on timestamp between cal.tbegin and cal.tend
group by cal.tbegin, cal.tend;