Adding time to MySQL datetime

Question

I had an issue where the server time wasn't set to the correct timezone and now need to update the created datetime for all users. New users from here on in will be using the correct timezone.

Essentially, I need it to run through all rows in table users and update column created with + 11 hours

I've tried this but it errors:

select `created` FROM `users` + INTERVAL 11 HOUR

Sithu · Accepted Answer · 2014-11-11 04:51:46Z

1

You can use a MySQL function DATE_ADD:

UPDATE users SET created = DATE_ADD(created, INTERVAL 11 HOUR)

answered Nov 11, 2014 at 4:51

Sithu

4,88210 gold badges68 silver badges113 bronze badges

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

2 Comments

TFK300 Over a year ago

Thanks @Sithu, getting error "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HOURS)' at line 1"

Sithu Over a year ago

@TFK300 Sorry, use HOUR without S.

Collectives™ on Stack Overflow

Adding time to MySQL datetime

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related