Last Updated: September 27, 2021
·
4.296K
· bashir

Adding time interval to timestamp column

this applies to postgres only.

You may need to update a timestamp/datetime field with some interval. Let's say we want to delay all of our auctions by 10 days. we can use active record:

Auction.where("auction_date> ?", Time.at(0)).update_all("auction_date = (auction_date +#{10.days})")

or direct database query(postgres)

Auction.connection.execute("UPDATE auctions SET auction_date = (auction_date + '10 DAYS'::interval) WHERE auction_date > 'epoch'")

there are two postgres features we used here:

'10 DAYS'::interval

which translates to a typecasted interval value that can be added to timestamps.

and

'epoch'

which is a special value in postgres representing the epoch time.