INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Odd result on simple time query

Odd result on simple time query

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

CODE

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:


CODE --> SQL

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

CODE --> SQL

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

CODE --> sql

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

RE: Odd result on simple time query

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.

RE: Odd result on simple time query

(OP)
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.

CODE

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

RE: Odd result on simple time query

Timestampdiff should be fine for hours.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close