triggers in mysql with timestamps -

triggers in mysql with timestamps -

i have main table sick getting values every 1 minute,here want utilize triggers calculate every hr info automatically.

timestamps energy 2014/11/3 04:00 10 2014/11/3 04:01 20 2014/11/3 04:02 30 2014/11/3 04:03 40 2014/11/3 04:04 50 2014/11/3 04:05 60

my output should this

time stamp energy 2014/11/3 04:05 210

my code

select concat( hour(timestamp), ' ', concat( hour(timestamp), ':05:00' ) ) time_frame, count(*) temp grouping date(timestamp), hour(timestamp)

i want utilize triggers

create table if not exists `hourly` ( `d` date not null, `h` int(11) not null, `value` int(11) not null, primary key (`d`,`h`) ) engine=myisam default charset=utf8 collate=utf8_czech_ci; create table if not exists `main` ( `timestamp` datetime not null, `value` int(11) not null, primary key (`timestamp`) ) engine=myisam default charset=utf8 collate=utf8_czech_ci; drop trigger if exists `cache_sum_hourly`; delimiter // create trigger `cache_sum_hourly` after insert on `main` each row begin insert hourly (d,h,value) values ( date(new.timestamp), hour(new.timestamp), new.value ) on duplicate key update hourly.value=hourly.value+new.value; end // delimiter ;

but not sure how needed. normally, should able count main table.

mysql triggers