Helpful Information
 
 
Category: PostgreSQL Help
postgres date comparisons (resolution of 1 second)

Hi,

I'm writing a form processing app in php/postgres and i wanted to implement a check on the post->db write functions in my code to prevent users double clicking on the submit button and causing repeated entries in the db.

What I'm thinking is that when I issue a write to the db, I'll do a quick select to check if there already exists a record for this user and that record's date is exactly equal to (give or take a second) now, then not write it again.

BTW if anyone else get's this, and knows of a more robust method to combat against this sort of thing, I love to know.

Away, subtracting 1 from now() returns the date 1 day out. Bit too much. Subtracting a fraction from now() returns the date without the time. Now I've looked thru the date functions in postgresqls manual, but I could really do with seeing worked example (which the manual tends to be rather short on).

The date field in my table is of type timestamp, and to recap I want to programatically take one second off this value - preferabily in the sql statement, such that :

(simplified sql for illustation)

select * from table where now()-date <= 1

as in 1 second

Figured it out around ten minutes later :

select * from bla where id = '$id' and (now() - interval(reltime '1 second') <= date)

however this doesn't work on the old 'double click submit and end up with many sql inserts' for some reason, so I'm back to the drawing board :)










privacy (GDPR)