Hi,

How can I work out the time difference between the time 'now' and the date time stored in a timestamp field of a mySQL database.

The specific situation I have right now is working out how many minutes since a specific row has been updated.

Thanks,

Mike

    To answer my own question:

    If you have a table with a timestamp column called tstamp you can use the following in a query to get the difference in minutes between now and when the row was last updated:

    ROUND((unix_timestamp(NOW()) - unix_timestamp(tstamp)) / 60 ) as diff

    for example:

    select
    id,
    name,
    ROUND((unix_timestamp(NOW()) - unix_timestamp(tstamp)) / 60 ) as diff
    from mysoopatable
    where
    ROUND((unix_timestamp(NOW()) - unix_timestamp(tstamp)) / 60 ) >17

    will show records that have not been accessed for 17 minutes.

    The big question now, though is why can't I use diff in the where clause?

    Cheers,

    Mike

      Write a Reply...