[RESOLVED] Mysql Timestamp and PHP - Best way to display time

[Mr. Baggins]

I have searched the Net and come up empty-handed; I wonder if anyone here could assist me in resolving this issue.

I would like the data stored in a MySQL TIMESTAMP field to be called and displayed using PHP. I have already created the necessary MySQL columns in a table to record the date of creation and the date of modifiction, and a script to insert them into the table:

$query = "INSERT INTO articles VALUES
('','$page_title','$content_title','$author',null,null,'$content')"; 

(See attached graphic for a screenshot of the corresponding table.)

This process works great; however, my problem arises when I retrieve the data from the database. The date is stored in this format:

2010-12-24 06:51:26, and when I pull the date into the webpage by merely assigning the data in that field a variable and calling the variable, it is displayed on the webpage exactly as it is stored in the database.

Does anyone

1) know how to change the timezone and / or format the date to a more user friendly format - between the time the date leaves the database and the time that it is printed on the page?

2) have a better way of storing dates than the MySQL method that I am using?

All help will be greatly appreciated.

[Dasher]

Here is one way;

date_default_timezone_set('America/Chicago');
$query = "SELECT UCASE( DATE_FORMAT(Publish_Up,'%b %d, %Y  %l:%i %p'))AS publishDate FROM table WHERE something = 'something'"; 

// get the data with msql_query() and mysql_fetch_array() the usual way;

echo $a_row['publishDate']; 

The date will display like this DEC 14, 2010 3:22 PM

[Charles]

That's the best way but I find myself using PHP to grab data using lots of slightly different versions of SQL. I can never remember which method of formatting the date goes with which version of SQL so I post process with SQL.

echo date ('j M Y', strtotime ($a_row['publishDate']));

[Dasher]

Also the mysql server location my affect the times that are being stored. I use this little trick to bump the time by 1 hour (the server is on MST, and I am on CST) :

$query = "INSERT INTO table (`publishUp`) VALUES (DATE_ADD(NOW(),INTERVAL 1 HOUR)) ....etc. ";

[Charles]

Also the mysql server location my affect the times that are being stored. I use this little trick to bump the time by 1 hour (the server is on MST, and I am on CST) :

$query = "INSERT INTO table (publishUp) VALUES (DATE_ADD(NOW(),INTERVAL 1 HOUR)) WHERE ....etc. ";

At that point it becomes much easier to post process.

[Dasher]

There is some advantage to doing the one hour bump on the INSERT, MySQL handles the possible date change as well, so NOW() where now = 2010-12-24 23:50 + one hour ='s 2010-12-25 00:50 after the bump.

[Charles]

MySQL handles the possible date change as wel

As does PHP's strtotime().

[Mr. Baggins]

Thanks, Dasher and Charles!

Your posts were quite instructional; I learned a lot from them.

I ended up merely assigning a variable to the data directly from the table, and then formatting it later like so:

<?php echo date("F j, Y", $date_created); ?>

Happy New Year!

Mr. Baggins