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

secretlydead · 05-14-2012, 02:49 AM

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?

Tinkster · 05-14-2012, 03:56 PM

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

secretlydead · 05-14-2012, 08:40 PM

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.

Tinkster · 05-14-2012, 08:45 PM

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

Nominal Animal · 05-15-2012, 12:35 AM

Quote:

Originally Posted by Tinkster

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.

AnanthaP · 05-15-2012, 02:02 PM

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

OK

secretlydead · 05-15-2012, 09:55 PM

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.

Tinkster · 05-15-2012, 11:26 PM

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

Code:

alter table FOO add unique index(VALUE, date(TIMESTAMP));

should do the trick; discarding the time for the constraint.

Cheers,
Tink

AnanthaP · 05-21-2012, 07:04 PM

Actually,

Code:

Insert into <table> ([field_list]) values (field1, [field2..]]) WHERE m.date_value <> (select max (a.data_value) from <table>)

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