Search code examples
phptimestampmktime

PHP swap month and day in timestamp

Right. I was inserting a load of data into a MySQL DB and used the following to generate the timestamp:

$stamp = mktime($t[0], $t[1], $t[2], $d[2], $d[1], $d[0]);

Unfortunately, the day and month were mixed around, and below is the correct timestamp.

$stamp = mktime($t[0], $t[1], $t[2], $d[1], $d[2], $d[0]);

It is about 5,000 records. What is the easiest way to do a bulk update and correction?

Thanks a lot!

Solution

  • Did the database engine allow you to insert impossible dates, or didn't you have any date with the Day field > 12?

    In any case, you can probably fix it with one update statement, but the syntax is dependant on the database engine you use.

    For MySQL you would use:

    UPDATE myTable SET dateColumn = STR_TO_DATE(DATE_FORMAT(dateColumn, '%d-%c-%Y %T'), '%c-%d-%Y %T')