[FIXED] Set a new different Date to each of ALL elements in my database in PHPMyAdmin

Issue

I have built a Database using PHP that has already over 600 films, 300 actors and over 1200 relations (actors in film). I added films in groups using PHPMYADMIN.

As you could see in the image lots of films have the same value in the "created_at" and "updated_at" columns. This is a problem when we pull films based by "date created" or "updated" or make a programm for clients base in date. I should have made those columns "unique" at the beginning, but I did not.

Now I need to change those values manually will take a long time and nerves. PHPMYADMIN do not let me edit the field directly in place. I have to do it with one or more SQL statements

The question is:

How could I:

Order ALL the films by ID ASC, then SET the "created_at" and the "updated_at" columns of each film with a different value (12 hours difference: two films a day), the first film will be the id=1 STARTING AT 2016-01-01 00:00:00?

At the end I want to have something like:

id    name            created_at                    updated_at
1   FilmName1       2016-01-01 00:00:00         2016-08-31 00:00:00
2   FilmName2       2016-01-01 12:00:00         2016-01-01 12:00:00
3   FilmName3       2016-01-02 00:00:00         2016-01-02 00:00:00
4   FilmName4       2016-01-02 12:00:00         2016-01-02 12:00:00
5   FilmName5       2016-01-03 00:00:00         2016-01-03 00:00:00
...
n   LastFilname     the resulting date           the resulting date

It should be in PHPMyAdmin. The "created_at" and "update_at" values could be the same.

Solution

Something like this should do the trick:

update table set
  created_at = DATE_ADD('2016-01-01 00:00:00', INTERVAL id*12 HOUR),
  updated_at = DATE_ADD('2016-01-01 00:00:00', INTERVAL id*12 HOUR)

Id is used to determine the date

Hope this helps!

Answered By - Wouter