Update timestamp values by subtracting 4 hours from each record

Question

I have a created_at timestamp field. I need to update and subtract 4 hours from every record.

This is about updating the records, not changing the results of a query.

So 2014-08-20 18:00:00 would become 2014-08-20 14:00:00.

And 2014-08-21 03:00:00 would become 2014-08-20 23:00:00.

That's not duplicate. The other question connected here is just to add/subtract and show them. Here, it is about making the changes to the field itself. — SenG, Jul 25 '18 at 16:42
@SenG indeed. Edited the question and started a vote to reopen the question. — GG., Sep 01 '20 at 20:32

score 16 · Accepted Answer · answered Aug 21 '14 at 16:44

16

You can use a simple UPDATE statement:

UPDATE yourtable SET created_at = created_at - INTERVAL 4 HOUR;

This will update all rows accordingly to your needs:

From the documentation to DATE_ADD

Date arithmetic also can be performed using INTERVAL together with the + or - operator:

date + INTERVAL expr unit
date - INTERVAL expr unit

answered Aug 21 '14 at 16:44

VMai

oh hey vmai! :) nice answer.. beat me by a few seconds! – John Ruddell Aug 21 '14 at 16:50
1

@JohnRuddell And *Ende Neu* did beat me my some seconds, so I upvoted you both. – VMai Aug 21 '14 at 16:55
1

thanks reciprocated :) – John Ruddell Aug 21 '14 at 16:57
For anyone else looking for the equivalent syntax in postgres: `UPDATE yourtable SET created_at = created_at - INTERVAL '4 HOURS'` – GG. Sep 01 '20 at 20:37

score 4 · Answer 2 · answered Aug 21 '14 at 16:44

4

You can use INTERVAL to specify the time that needs to be subtracted:

UPDATE myTable
SET created_at = created_at - INTERVAL 4 HOUR

answered Aug 21 '14 at 16:44

Ende Neu

John Ruddell · Answer 3 · 2014-08-21T16:51:37.763

3

few ways you can do this.

you can set the date equal to another date that is interval'd 4 hours earlier like so

using adddate()

UPDATE table SET created_at = ADDDATE(created_at, INTERVAL -4 HOUR);

using date_sub

UPDATE table set created_at = DATE_SUB(created_at, INTERVAL 4 HOUR);

using just interval

UPDATE table SET created_at = (created_at - INTERVAL 4 HOUR);

edited Aug 21 '14 at 16:51

answered Aug 21 '14 at 16:44

John Ruddell

3 Answers3