Datetime Fix

PHP
1

So I accidentally converted about 140,000 timestamps to zeros trying to fix a problem with 2010 being greater than/less than 2009.

Now I’m left with a problem of converting date entries prior to 2010 to TIMESTAMP format. I tried this:

$date = date(“YYYY-MM-DD HH:mm:SS”, strtotime($active[“datestamp”]));

but it resulted in:
1969196919691969-Dec

the old dates look like this:
12-31-2009 04:59
But need to look like this:
2010-01-02 21:37:22

Hope someone can help :blush:

2

I assume this “datestamp” column is a string (varchar/char/text) of some sort?

You can use MySQL’s STR_TO_DATE function and tell it the format of the dates it’s parsing.

3

Originally it was a varchar, so the original entries look like this:
12-31-2009 04:59

Now, entries are going into a TIMESTAMP field with the current timestamp. I need to convert all the old entries.

4

http://www.php.net/manual/en/function.date-parse-from-format.php
then just build the new date from array

5

If he uses STR_TO_DATE this can be done with a single UPDATE query and no PHP code or loops.

UPDATE tablename SET newdate = STR_TO_DATE(datestamp, ‘format tokens here’)

6

Thanks Dan, I tried:
UPDATE commenttableBU SET datestamp = STR_TO_DATE(datestamp, ‘%Y%m%d%H%i%s’)

but it just cleared all the datestamps. :sick:

7

Wouldn’t it be…

UPDATE table SET good_column = TIMESTAMP(STR_TO_DATE(bad_column, '%m-%d-%Y %h:%i'))

?*

*not au fair with SQL.

8

Thanks… this is the first time its actually converted the dates.

I’m testing it from one column to the next and it converted 43,000 of the 140,000 rows. Any idea why not all?

Here’s the MySQL:
UPDATE commenttableBU SET commenttableBU.newdate = TIMESTAMP(STR_TO_DATE(commenttableBU.datestamp, ‘%m-%d-%Y %h:%i’))

Thanks

9

You’re probably using a 1-digit format code for a 2-digit number, or a 2-digit format code for a 1-digit number

10

I would guess they don’t match the format provided, care to share some of the entries which didn’t receive a converted value?

Edit:

Or that. :stuck_out_tongue:

11

That was my first thought (and maybe I’ve been staring too long) but they seem to be about the same:

Did not get converted:
12-14-2008 00:59

Got converted:
12-14-2008 01:37

to:
2008-12-14 01:37:00

12

You were right. It was the H, thanks so much guys!

13

Bah, it was all Dan. tips hat

Glad you’re sorted though, maybe next time, you’ll be a little more cautious with those update queries huh! :smiley: