List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 19 2002 6:02pm
Subject:RE: NOW()-TIMESTAMP does not return accurate results
View as plain text  
At 9:36 -0800 12/19/02, Troy Kruthoff wrote:
>  > At 23:39 -0800 12/16/02, Troy Kruthoff wrote:
>>  >  >Description:
>>  >   Invalid reporting of date calc        
>>  >>How-To-Repeat:
>>  >   note: SESSIONTS is TIMESTAMP type
>>  >
>>  >   SELECT (NOW()-SESSIONTS) FROM WEBSESSIONS;
>>
>>  What leads you to expect that this should yield any useful result?
>
>
>According to the documentation of the NOW() function:
>
>"Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'
>or YYYYMMDDHHMMSS format, depending on whether the function is used in a
>string or numeric context"

YYYYMMDDHHMMSS is not a "value in seconds".  It's a date and time value
in numeric form.

>
>Assuming SESSIONTS is of type TIMESTAMP, "SELECT (NOW()-SESSIONTS)"
>should return the differences in seconds....  But it does not, in fact I
>can not determine what it us returning, it does not appear to be any
>accurate measurement of time.
>
>Troy

To do what you want, it depends on whether or not any of your values
are earlier than 1970.  If not, then you can use

UNIX_TIMETAMP(NOW()) - UNIX_TIMESTAMP(SESSIONTS)

Otherwise you'll need to bust up the values into date parts and time
parts and the expression is more complex:

((TO_DAYS(NOW()) - TO_DAYS(SESSIONTS)) * 24*60*60)
+ TIME_TO_SEC(NOW()) - TIME_TO_SEC(SESSIONTS)

Solutions above adapted from MySQL Cookbook, pp 256-257,
I hope I didn't goof them up. :-)
Thread
NOW()-TIMESTAMP does not return accurate resultsTroy Kruthoff17 Dec
  • re: NOW()-TIMESTAMP does not return accurate resultsEgor Egorov17 Dec
  • Re: NOW()-TIMESTAMP does not return accurate resultsPaul DuBois17 Dec
RE: NOW()-TIMESTAMP does not return accurate resultsPaul DuBois19 Dec