...

View Full Version : Regex issue - Converting a written date to a timestamp



The Reverend
03-18-2007, 09:48 PM
I have a problem, somehow through one of my scripts, all the timestamps in my database were set to 0000-00-00 00:00:00. I can fix this because one of the fields contains the written date in this format "Wednesday, June 7, 2006", I think I need to make a regex expression that can read that and generate a new timestamp from it, I'm just not sure where to start because I've barely used regex. Some of the dates are not in that format because it contains over two years worth of data, and I want to just delete the ones that are not in that format as well. I'd appreciate any help here, I'm kind of stumped. Once I match the expression I can generate the timestamp, but I don't know how to match it.

Fumigator
03-18-2007, 11:30 PM
Just use strtotime() (http://us3.php.net/manual/en/function.strtotime.php) and convert the resulting unix timestamp.

dniwebdesign
03-19-2007, 04:15 AM
Here are a few steps you can take to do this and convert it.

Take the written date out of the database...
Store it in a variable
Convert it to a timestamp via strtotime() (http://us3.php.net/manual/en/function.strtotime.php)
Convert the timestamp to your format using date() (http://www.php.net/date)
Example:
date("Y-m-d h:i:s",$date);


Below is a full example of how I would do it:


<?php
$mysql = mysql_query("SELECT * FROM yourtable");
while($row = mysql_fetch_array($mysql)) {
$thedate = $row["fulldaterow"];
$timestamp = strtotime($thedate);
$new = date("Y-m-d h:i:s",$date);
$update = mysql_query("UPDATE yourtable SET timestamp='$new' WHERE id='".$row["id"]."'");
}
?>

CFMaBiSmAd
03-19-2007, 04:55 AM
You can also use the mysql STR_TO_DATE(str,format) function to convert your values in a known format into a DATETIME value.

You could for example, create a new column to hold your DATETIME values and run a query that finds (some WHERE clause, such as greater than a starting ID number...) the existing values in your format and populates the new DATETIME column using the STR_TO_DATE function value.

If your older values are in a known format that you can write a WHERE clause to find, you should also be able to use a STR_TO_DATE format to convert them as well.

Once you have all the old formated data converted and populated into the new DATETIME column, you can delete the old column.

The Reverend
03-19-2007, 10:53 PM
Here are a few steps you can take to do this and convert it.

Take the written date out of the database...
Store it in a variable
Convert it to a timestamp via strtotime() (http://us3.php.net/manual/en/function.strtotime.php)
Convert the timestamp to your format using date() (http://www.php.net/date)
Example:
date("Y-m-d h:i:s",$date);


Below is a full example of how I would do it:


<?php
$mysql = mysql_query("SELECT * FROM yourtable");
while($row = mysql_fetch_array($mysql)) {
$thedate = $row["fulldaterow"];
$timestamp = strtotime($thedate);
$new = date("Y-m-d h:i:s",$date);
$update = mysql_query("UPDATE yourtable SET timestamp='$new' WHERE id='".$row["id"]."'");
}
?>


Thanks, I used a variation of that and got the desired results.

aedrin
03-20-2007, 04:04 PM
I'd suggest using the STR_TO_DATE(), although you probably already coded the other. ;)

If you don't have to take the data out of MySQL, that is usually better.

Although I'm curious as to why you stored the date in 2 fields (even though in this case it was good luck with bad design).

P.S.: Your signature would create an E_NOTICE, as assigning an object by reference does not make sense (since objects are always assigned by reference). Don't spread bad code ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum