[RESOLVED] covert or fix dates in database

marcnyc

Hello,
I have a script written a long time ago and the dates for every item stored in the database are stored in text format (the column Type is defined as "text"):
January 2 2009

I am in the process of syndicating this content so I would like to know whether it would make more sense to convert ALL the dates in the database to RFC 2822 or whether it would make more sense to leave the database the way it is and then write a script to convert that text string into an RFC 2822-compliant date.

Thank you

devinemke

create a new field in your table using whatever the native date type your RDBMS supports
pull all of the old text dates from the table
while looping thru the results run each text date thru [man]strtotime[/man] then thru [man]date[/man] to properly format and then write the result back to the new date field

marcnyc

can you give me an example of how to do the loop?

devinemke

$result = mysql_query('SELECT id, old_date FROM table') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	$new_date = date('Y-m-d', strtotime($row['old_date']));
	mysql_query("UPDATE table SET new_date = '$new_date' WHERE id = " . $row['id']) or exit(mysql_error());
	echo 'ID #' . $row['id'] . ' update OK<br>'; 
}

another thing to consider is that my may want to store all of your dates as GMT in which case you should use [man]gmdate[/man] rather than [man]date[/man]

bradgrafelman

If you were using MySQL, you could also just execute this query:

UPDATE myTable SET new_date=STR_TO_DATE(old_date, '&#37;M %e %Y')

marcnyc

Thank you BOTH for your EXCELLENT suggestions!
I haven't updated my database yet but I have used devinemke suggestion within PHP to actually convert the date within PHP, after I pulled it from the database, and as soon as I will decide to upgrade the database structure I can use either his loop or bradgrafelman's quick and easy suggestion!
Thank you so much to both of you!

marcnyc

Hey guys, actually, I decided not to procrastinate this and do it now, so my only question is:
what is best practice when it comes to storing dates in the database? Is it best to save them in Unix format? or in RCF2822 format?
What kind of format would bradgrafelman's and devinemke's functions create?

devinemke

you should use whatever is the native date format of your RDBMS

marcnyc

what is the native format of MySQL?

devinemke

read up on mySQL Date & Time Types

marcnyc

So if I understand that correctly I should store the date in unix timestamp format and then use php functions to get a human readable date and time out of that. Is that correct?

Weedpacket

I think devinemke was suggesting you use one of the MySQL Date & Time Types. MySQL doesn't have a "unix timestamp format" type.

marcnyc

bradgrafelman;10898757 wrote:
If you were using MySQL, you could also just execute this query:
UPDATE myTable SET new_date=STR_TO_DATE(old_date, '%M %e %Y')

can I run this query from directly within phpMyAdmin by just substituting the "myTable" with the name of my table, the "old_date" with the name of the column in which the date is stored and before I run the query change the structure of that column from string to DATE?

In other words in my database there is a table called 'reviews' and within that table a column called 'date' which is set to TEXT and contains strings. If I want to convert that into DATETIME format, can I do the following?
1. change the column type from TEXT to DATETIME
2. run this query from the SQL tab of phpMyAdmin:

UPDATE reviews SET date=STR_TO_DATE(date, '%Y-%M-%D %H:%m:$s')

is this correct?
can I do it on the same column and update all rows in that column? or do I need to create a new column to store the converted values?

marcnyc

or should I just use devinekme's function and make a new column?