Ive attached the gateway error below. Basically I need to come up with an alarm duration time for my alarms by calculating the difference between the active_time and the clear_time fields in the alarmlog database table. However, if I get a difference where the hours portion is more than two characters, FactoryPMI complains. I can run the same query in mysql and get the correct result. I will note however that if the duration time is greater than 838:59:59, mysql will alert me with a warning saying the value was truncated. I am assuming that 838:59:59 is the max value for a timestamp field. How do I get around working with these 3 digit hour portions of the timestamp?
Thanks,
Syd
Gateway Error 301:
SQL error for "select *,
sec_to_time(secs/count) as avgtime,
sec_to_time(secs) as tottime
from
(select tbl_device.device, count(alarmlog.item_name) as count, tbl_FaultClass.Class,
–sec_to_time(min(timestampdiff(second, alarmlog.active_time, alarmlog.clear_time))) as mintime,
–sec_to_time(max(timestampdiff(second, alarmlog.active_time, alarmlog.clear_time))) as maxtime,
sum(timestampdiff(second, alarmlog.active_time, alarmlog.clear_time)) as secs
from alarmlog
join tbl_alertdetails
on alarmlog.item_name = tbl_alertdetails.item_name
join tbl_device
on tbl_device.ndx = tbl_alertdetails.deviceid
join tbl_faultclass
on tbl_faultclass.ndx = tbl_alertdetails.classid
where tbl_alertdetails.alttypeid = 2
and (active_time is not null and
clear_time is not null)
group by tbl_device.device, tbl_FaultClass.Class) as zm
": Cannot convert value ‘216:39:30’ from column 7 to TIMESTAMP.
On: Reporting_08_R102_DailyCBISReliability.Root Container.Report Viewer 4.Data_Flt