TIMESTAMP Problem on Reports

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?


Gateway Error 301:
SQL error for "select *,
sec_to_time(secs/count) as avgtime,
sec_to_time(secs) as tottime
(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

1st of all, the sec_to_time function is defined to work only within the constraints of the MySQL TIME datatype.

As for the other error (cannot convert value to TIMESTAMP), the problem here is that FactoryPMI doesn’t have any sort of “Time” datatype, and so its trying to ask the JDBC driver to turn it into a Timestamp (aka Date) instead, which isn’t working. You don’t really want a date - you want a string! To that end, you could cast the results of sec_to_time to a string.

The other option would be to do the formatting yourself, by taking the seconds value and dividing it by values to create the hh:mm:ss format manually using a big CONCAT function. This would be the only way to get around the limitations of the MySQL TIME datatype mentioned above.

Breaking it up into 3 separate integers is how i was getting around it now. Strings are not a good solution because I still need to perform min, max, avg, and total functions on them, and converting everything to an integer to do the math is kind of a hastle.

Can you think of any way that if the hours were a 3 digit number, i could just default the timestamp to 99:99:99?

You could use a CASE to check if the number of seconds was too high, and then return the fallback of 99:99:99