CODEWITHSUNDEEP
sqleventspostgresqltimesql-update
Mark Harold C. Rivera
Mark Harold C. Rivera

Reputation: 291

Updating rows based on time in PostgreSQL

What is the best way to update tables based on time in PostgreSQL?

For example I have a database with the following records.

ColumnA  StartTime  EndTime
123      02:05:01   02:06:01   
456      02:06:01   02:07:01

Is it possible to update the values of ColumnA based on the EndTime column, possibly a time event? For example if the time of the server is 2:06:01 then the value 123 will be incremented by 100.

I tried using pgagent but I think it is not suitable for time specific events(seconds) especially if the database has a large number of rows.

Can you suggest solutions for this. Thanks.

Upvotes: 1

Views: 328

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658887

You want to trigger the UPDATE by server time.
One way would be to create a cronjob for that. Invoke crontab -e as user postgres (or whatever user you want to run it) on a Linux machine and schedule something like this at a time of your choosing:

psql mydb -p5432 -c 'UPDATE tbl SET columna = columna + 100 WHERE columna = 123'

BTW, I would advice not to use mixed case identifiers in PostgreSQL.

Upvotes: 2

Related Questions