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. :-)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
