Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts

    Datetime calculation gives weird result! Help!

    I am using MySql 5.1 on Debian Lenny

    I noticed some weird behaviour in my code, so I investigated.

    What it boils down to is this:

    let's say there's a table called "abc" which contains a single column, called "whenStored" which is a DATETIME.

    let's say there's one row in the table, and whenStored is set to the value 2011-05-18 13:22:13.

    Consider the following query:

    select now(), whenStored, now()-whenStored from abc;

    I get the following three values:

    2011-05-19 15:02:40
    2011-05-18 13:22:13
    1018027.000000

    Now it's that last value that has me completely confused ... given that now() here is nearly 26 hours after whenStored, then I would expect the value of now()-whenStored to be: a little less than 26*60*60, which is 93600.

    But I'm getting 1018027, which is nearly 283 days!!!

    WTF?!

    As you can see I'm a little confused and frustrated ...

    Any help gratefully received!

    Thanks in advance.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,860
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    You can't just subtract or add two date/time values as you do in the case of normal numbers. There are built-in functions in mysql for this, like datediff() and timediff()
    Last edited by abduraooft; 05-20-2011 at 03:56 PM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    Ah yes! Many thanks. I've managed to do it using the unix_timestamp function.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •