mysql insert only if the values are different than the max timestamp
 

I'm trying to insert a row into a mysql table only if the most recent row (the row with the most recent timestamp) differs from the row i'm trying to insert.

can i do that with a single query, or do i have to use a trigger/script?

Just create an appropriate unique constraint and let your insert fail.
No farting around needed.

that wouldn't work. you might have values like this:

from 2010-01-01 to 2010-06-01 - value1
from 2010-06-02 to 2010-07-01 - value2
since 2010-07-02 - value1

so the database would be like:
value ts
value1 2010-01-01
value2 2010-06-02
value1 2010-07-02

having a unique restraint on the values would preclude entering the third entry there, while i want a record of all the values and the times they existed.

Not if you make the constraint over both columns ...

And/or use a separate column for precise timestamps, with the constraint covering only the event column(s) and a less precise timestamp column.

The unit in the less precise timestamp is the unit during which you don't want duplicate reports, for example a day. It does not need to be real timestamp either; you can just divide the number of seconds since epoch ("Unix time") by the number of seconds in your no-duplicate duration to get an integer suitable for the constraint timestamp. Just make sure it is large enough so it won't wrap.

This is not really a time stamp (which is created by the system) you know. Its just a set of dates.

OK

A constraint over both columns wouldn't work either, because then i might get this:

value1, 2010-01-01 HH:MM:SS
value2, 2010-06-02 HH:MM:SS
value1, 2010-07-02 HH:MM:SS
value1, 2010-07-03 HH:MM:SS

which i also don't want. If the newest entry has the same value as the previous latest entry, then the new entry should be discarded.

this way, you have a record of all the changes and what the value was at any particular time, with as little space used as possible.

You keep talking about time-stamp, but show us date; now you bring HH:MM:SS
into the equation; which is it?

Either way, I still think that something like
should do the trick; discarding the time for the constraint.


Cheers,
Tink

Actually,

and so on
http://stackoverflow.com/questions/9...th-a-condition
This is mySql

Oracle has an Insert when clause:
http://psoug.org/reference/insert.html

OK