mysql - Sum value by hourly for IST data in UTC database -

my db in utc timezone , data inserting in utc time. want sum values , group hourly ist time data. below,

id  data_id value servertime 1   2       100   2016-05-02 18:30:54 2   2       100   2016-05-02 18:45:54 4   2       200   2016-05-02  19:00:54 5   2       100   2016-05-02 19:15:54 6   2       100   2016-05-02 19:30:54 7   2       100   2016-05-02  19:40:54  

query

select sum(value) value, servertime date data_table  data_id=2  , servertime between convert_tz('2016-05-03 00:00:01','+00:00', '-05:30')  , convert_tz('2016-05-03 10:45:24','+00:00', '-05:30')  group year(date),month(date),week(date),day(date),hour(date); 

above query giving result :

200     500 

but expecting output :

600     100 

because ist 12 = utc- 05:30 means 18:30 19:30 here query calculating 18:30 19:00, 19:00 20:00, 20:00 21:00 not accuracy value.

i want calculate value 18:30 19:30 , 19:30 20:30 accuracy value ist time data.

how solve this?

by ist, assume mean india standard time, 5 hours , 30 minutes ahead of utc. fixed offset, +05:30, not -05:30. you're results incorrect because have sign inverted.

the convert_tz function accepts of:

• 'system' local system time zone
• fixed offsets in standard iso 8601 format, have positive offsets east of utc, such '+05:30' india, or '-10:00' hawaii.
• named time zones, using standard iana/olson tzdb identifiers, assuming time zone tables populated. india's 'asia/kolkata', eastern time 'america/new_york', etc. using option requires mysql time zone tables populated, per the documentation.

in general, named time zones preferred because accommodate changes in offset due daylight saving time , historical changes. however, india has been fixed @ +05:30 since 1942 , isn't change in near future, it's reasonable use fixed offset approach if only time zone need deal with.

also note "ist", many time zone abbreviations, ambiguous. can mean india standard time (+05:30), ireland standard time (+01:00) or israel standard time (+02:00). note ireland standard time daylight time zone offset, despite having name "standard" in it. avoid confusion, please specify particular location when referring ist in future, , don't ever expect computer able distinguish them.