Rails/PostgreSQL - Query to update all dates adding 1 day

Question

I want to execute an update_all query to update a datetime column adding 1 day to the current value of this column on all rows.

I could achieve that using .each and .update, but it would be more efficient to perform in a single query.

Is it possible to achieve that using Rails and PostgreSQL?

This is trivial to express as a query. It should be pretty basic in Ruby as well. — Gordon Linoff
– Gordon Linoff, Commented Feb 27, 2018 at 15:11

Jim Jones · Accepted Answer · 2020-06-15 09:01:08Z

8

UPDATE table SET date_field = date_field + 1;

Or ..

UPDATE table SET date_field = date_field + INTERVAL '1 DAY';

answered Feb 27, 2018 at 15:18

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

just wondering, as I wasn't aware of such use; at first the +1 (without any specification) looks quite random

I also found it out by accidentally running SELECT CURRENT_DATE + 1 ; :-)

Ashik Salman · Accepted Answer · 2018-02-28 06:13:17Z

2

Rails way of updating all records

Model.update_all("column_name = (column_name + '1 DAY'::INTERVAL)")

This update query can be run via rails console.

Note: update_all method does not trigger Active Record callbacks or validations and it will not change updated_at timestamp.

answered Feb 28, 2018 at 6:13

Ashik Salman

1,88513 silver badges16 bronze badges