calculating NOW()-timestamp

philippo

I'm trying to calculate time in MySQL.

There's a timestamp(14) row called "last_access" in my table and I'm trying to get the seconds between now and that timestamp.

Like this it works fine:
UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(last_access)

but why wouldn't this work?:
NOW()-last_access

This returns some weird value (on the same day: /100=seconds, on another day it's a way too high value!)

philip

philippo

my problem forces me to use UNIX_TIMESTAMP() everywhere, but like this it won't work:

UPDATE mytable SET del_usr = 'Y', last_access = last_access WHERE (UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 0 HOUR))-UNIX_TIMESTAMP(last_access)) > 172800 OR (conf = 'N' AND (UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 0 HOUR))-UNIX_TIMESTAMP(date_start)) > 86400;

You have an error in your SQL syntax near '' at line 1

You could do me a great favor if you could check that huge query. Thanks!!

Philip

brandonschnell

you're lucky if thats a huge query for you. =)

looks like you've got an extra "(" in the second part of the query query.

philippo

thanx!!
That was the only problem! Sometimes I'm just missing one single character, like this ")" and I cannot figure it out, silly as I am!

I still don't know why I need to calculate seconds with UNIX_TIMESTAMP() and why it won't work without. any idea?

philip

brandonschnell

not really. UNIX_TIMESTAMP() makes it a simple mathematical operation but using NOW() involves a custom data type + an arithmetic operator that isn't aware how to handle the DATE types = corrupt date results. its like mysql is trying to do arithmetic on a string because it has no idea how to handle the string.