Odd result on simple time query

[CamaroLT]

I've been handed a project that deals with time stamps, and I'm getting a really odd result.

mysql> select * from (select timediff("2014-05-26 14:33:06", "2014-01-29 20:21:24")) a;
+--------------------------------------------------------+
| timediff("2014-05-26 14:33:06", "2014-01-29 20:21:24") |
+--------------------------------------------------------+
| 838:59:59                                              |
+--------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select timediff("2014-05-26 14:33:06", "2014-01-29 20:21:24");
+--------------------------------------------------------+
| timediff("2014-05-26 14:33:06", "2014-01-29 20:21:24") |
+--------------------------------------------------------+
| 2802:11:42                                             |
+--------------------------------------------------------+
1 row in set (0.00 sec)

The subquery is a copy/paste to a main query, but, I'm getting different results, and the 838 (I'm assuming) hours is what is throwing me off. The query I'm working with is:

select DocumentID, File, Title, Timestamp, ReviewedDate, ModifiedBy, Owner, timediff(Timestamp, ReviewedDate) TimeModifiedDelta from Document 
where Timestamp > now() - interval +3 day and Timestamp>ReviewedDate order by Timestamp desc;

I wanted to change it to

select *, hour(TimeModifiedDelta) HourDelta from (select DocumentID, File, Title, Timestamp, ReviewedDate, ModifiedBy, Owner, timediff(Timestamp, ReviewedDate) TimeModifiedDelta from Document 
where Timestamp > now() - interval +3 day and Timestamp>ReviewedDate order by Timestamp desc) theReults;

I then noticed that the HourDelta returned as ~800 hours instead of the 2800 hours I was working with before the subquery invention.

The purpose of the change was to try and get rid of duplication in the field results. The other way I can do this query as a single query is

select DocumentID, File, Title, Timestamp, ReviewedDate, ModifiedBy, Owner, timediff(Timestamp, ReviewedDate) TimeModifiedDelta, hour(timediff(Timestamp, ReviewedDate)) HourDelta from Document 
where Timestamp > now() - interval +3 day and Timestamp>ReviewedDate order by Timestamp desc;

I'm of the frame of mind of "If you've done the calculation once, why do it again?" which is why I'm on this particular optimization route, but the time is way off.

So for a formal question, why the difference on a literal/specific/exact time conversion? I don't see any correlation between 2802 and 838, the hours and minutes for 59:59 doesn't even add up, but I suspect that is a clue. I just don't see it.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[jpadie]

in the top query you will see there is a warning. type show warnings; after that query and you will see that the value has been truncated.

it is somewhat odd that the two alternatives do not both give truncated values however.

try timestampdiff instead. this is not limited by the boundaries of the TIME data type. perhaps use date_diff() instead.

 

[CamaroLT]

Thanks for the reply, and I see the warning and I understand it (Timestamp doesn't like >24 hours or numbers larger than 3 digit hours value, or the integer value of what is returned doesn't fit in the bits, etc), but I'm not sure WHY I'd be getting a warning or a truncated result at all considering I'm getting a proper result with the hour() statement. I'm asking the engine for whatever results are being returned, unformatted, as is, raw type. If I'm being returned something that isn't exactly a properly formatted time stamp, I get that I may not be able to find the exact cast manually within a program, but MySQL should be able to figure out what the cast is from an inner/sub query. Apparently I'm mistaken in that regard.

I can't use date_diff as I need a resolution of hours at least. I see the MySQL site reports for version 5.5 that timestampdiff does exist, but apparently, mysql 4.3 doesn't. I can't update the database engine, so I guess I'm going to have to do the single query as I posted.

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2802:11:42' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[jpadie]

Timestampdiff should be fine for hours.