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. :-)
