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

This site is best viewed in a modern browser with JavaScript enabled.

[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.

mysql_table.PNG

22kB

Dasher

Here is one way;

date_default_timezone_set('America/Chicago');
$query = "SELECT UCASE( DATE_FORMAT(Publish_Up,'&#37;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

Dasher;1130899 wrote:
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

Dasher;1130935 wrote:
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