See More See More - 7 days ago 5
MySQL Question

TIMESTAMPDIFF returns blob in mysql

I am having a problem in my query, I want to display the two tables and group it by same date, then, add the difference of start and end time. Here's my tables:

equipment_schedule

id | equipment_id | booked_date | created_at | updated_date
1 | 1 | 2016-11-21 | 2016-11-21 21:21:21 | 2016-11-21 21:21:21
2 | 1 | 2016-11-22 | 2016-11-22 21:21:21 | 2016-11-22 21:21:21
3 | 1 | 2016-11-23 | 2016-11-23 21:21:21 | 2016-11-23 21:21:21
4 | 1 | 2016-11-24 | 2016-11-24 21:21:21 | 2016-11-24 21:21:21
5 | 1 | 2016-11-25 | 2016-11-25 21:21:21 | 2016-11-25 21:21:21
6 | 1 | 2016-11-26 | 2016-11-26 21:21:21 | 2016-11-26 21:21:21
7 | 1 | 2016-11-27 | 2016-11-27 21:21:21 | 2016-11-27 21:21:21
8 | 1 | 2016-11-28 | 2016-11-28 21:21:21 | 2016-11-28 21:21:21
9 | 1 | 2016-11-29 | 2016-11-29 21:21:21 | 2016-11-29 21:21:21
10 | 1 | 2016-11-30 | 2016-11-30 21:21:21 | 2016-11-30 21:21:21


equipment_operation

id | equipment_id | start_time | end_time | creation_date | updated_date
1 | 1 | 2016-11-21 09:20:00 | 2016-11-21 23:59:59 | 2016-11-21 09:00:00 | 2016-11-21 09:00:00
2 | 1 | 2016-11-22 09:20:00 | 2016-11-22 13:00:00 | 2016-11-22 09:00:00 | 2016-11-22 09:00:00
3 | 1 | 2016-12-01 09:20:00 | 2016-12-01 13:00:00 | 2016-12-01 09:00:00 | 2016-12-01 09:00:00
4 | 1 | 2016-11-22 14:00:00 | 2016-11-22 18:00:00 | 2016-11-22 14:00:00 | 2016-11-22 14:00:00


my query is this:

SELECT a.id, booked,
IF(a.diff = "diff", 0, a.diff) AS diff,
creation_date,
updated_date
FROM
(
SELECT equipment_operation.id,
(
SELECT IF (DATE_FORMAT(equipment_operation.creation_date, "%m-%d-%Y") = DATE_FORMAT(booked_date, "%m-%d-%Y"), booked_date, 0)
FROM equipment_schedule WHERE DATE_FORMAT(equipment_schedule.created_at, "%m-%d-%Y") = DATE_FORMAT(equipment_operation.creation_date, "%m-%d-%Y")
) AS booked, SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS diff,
equipment_operation.creation_date as creation_date, equipment_operation.updated_date as updated_date FROM equipment_operation
INNER JOIN equipment_schedule ON (equipment_schedule.equipment_id = equipment_operation.equipment_id)
GROUP BY DATE_FORMAT(equipment_operation.creation_date, "%m-%d-%Y")
UNION ALL
SELECT id, booked_date, "diff", created_at as creation_date, updated_at as updated_date FROM equipment_schedule
) a
GROUP BY DATE_FORMAT(a.creation_date, "%m-%d-%Y")


the diff returns blob, but when I remove the sum it will return the difference of start_time and end_time in seconds but it will give a wrong query because it is not added with the same date.

my output:

id | booked | diff | creation_date | updated_date
1 | 2016-11-21 | BLOB | 2016-11-21 09:00:00 | 2016-11-21 09:00:00
2 | 2016-11-22 | BLOB | 2016-11-22 09:00:00 | 2016-11-22 09:00:00
3 | 2016-11-23 | BLOB | 2016-11-23 05:58:09 | 2016-11-23 05:58:09
4 | 2016-11-24 | BLOB | 2016-11-24 05:58:16 | 2016-11-24 05:58:16
5 | 2016-11-25 | BLOB | 2016-11-25 05:58:16 | 2016-11-25 05:58:16
6 | 2016-11-26 | BLOB | 2016-11-26 05:58:17 | 2016-11-26 05:58:17
7 | 2016-11-27 | BLOB | 2016-11-27 01:56:19 | 2016-11-27 01:56:19
8 | 2016-11-28 | BLOB | 2016-11-28 01:56:19 | 2016-11-28 01:56:19
9 | 2016-11-29 | BLOB | 2016-11-29 01:57:34 | 2016-11-29 01:57:34
10 | 2016-11-30 | BLOB | 2016-11-30 01:58:17 | 2016-11-30 01:58:17
3 | null | BLOB | 2016-12-01 09:00:00 | 2016-12-01 09:00:00


my expected output is that the diff has a value.

Answer

Here is the problem with your query:

SELECT id, booked_date, "diff", ...
                         ^^^

You are performing a UNION between two tables, and in the first half of the union you have the sum of integers for the diff column while in the second half you have a string. In general, it is required that the types of all the columns are in agreement. One way around this would be to cast your timestamp difference term as char, i.e.

CAST(SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS CHAR(5)) AS diff,
Comments