0

I have a history table with a DATETIME column. Is it not possible to assign a variable within a DATEDIFF function? The following statement:

SET @LASTDATETIME='2000-01-01 00:00:00';
SELECT DATETIME, @LASTDATETIME, TIMEDIFF(DATETIME, @LASTDATETIME:=DATETIME) AS CALC, @LASTDATETIME FROM HISTORY

results in this result:

|DATETIME           |@LASTDATETIME       | CALC    | @LASTDATETIME
-------------------------------------------------------------------
2013-01-28 12:11:41 |2000-01-01 00:00:00 |00:00:00 |[BLOB - 19 B]
2013-01-28 12:11:44 |2013-01-28 12:11:41 |00:00:00 |[BLOB - 19 B]

I don't understand why it seems the assignment sort of worked as DATETIME is carried forward to the next record, but the CALC field failed, and after the assignment the value of @LASTDATETIME is a BLOB? Why doesn't this work as I'm expecting?

Thanks for the help.

user2053292
  • 1

1 Answers1

0

Try this:

SELECT @last AS `last`,
       TIMEDIFF(`datetime`, @last) AS `difference`,
       @last := `datetime` AS `current`
FROM history, (SELECT @last :='2000-01-01 00:00:00') n

Here is sqlfiddle

peterm
  • 91,357
  • 15
  • 148
  • 157
  • That works. What I'm actually trying to do is update the table based on the DATEDIFF, so as far as I can see I need to change @last within the function. I'm trying to update a column called tripid, if the timestamp is more than 10 minutes past the timestamp in the last record, the tripid increments, otherwise the record is considered part of the same trip. Here is a fiddle: [sqlfiddle](http://sqlfiddle.com/#!2/35918/1/0) (hopefully this works, sorry, newb) – user2053292 Feb 14 '13 at 00:01