Why don't you use the default 18 characters MySQL datetime data type : YYYY-MM-DD HH:MM:SS? Then you can convert it with substr() to any date and time form you want with maketime() PHP function.
Example :
2002-11-01 10:10:02
<?
$conn=dbconnect(); // sample function for mysql db connection
$result=mysql_query("SELECT timestamp FROM my_table WHERE bla....bla....");
$my_date=mysql_fetch_array($result);
$year=substr($my_date,0,4);
$month=substr($my_date,5,2);
$day=substr($my_date,8,2);
$hour=substr($my_date,11,2);
$minute=substr($my_date,14,2);
$second=substr($my_date,17,2);
$date="$month, $day $year"; // you can change the date format as you want here
$time="$hour:$minute:$second"; // ...and the time also ;)
echo "$date $time";
?>
use date("Y-m-d H:i:s"); instead when you insert a new data to your timestamp column (if you want to use datetime data type).
If have so many entries already with DD-MM-YYYY HH:MM:SS format in text data type, you can use regular expression to convert your entries to YYYY-MM-DD HH:MM:SS before you can convert to datetime data type.
Hope it helps! 🙂
