www.webdeveloper.com
Results 1 to 8 of 8

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

  1. #1
    Join Date
    Jul 2010
    Posts
    35

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

    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:

    PHP Code:
    $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.
    Attached Images Attached Images
    Last edited by Mr. Baggins; 12-24-2010 at 11:17 AM. Reason: forgot to attach graphic

  2. #2
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    Here is one way;
    PHP Code:
    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
    Last edited by Dasher; 12-24-2010 at 01:24 PM.

  3. #3
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,279
    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.
    Code:
    echo date ('j M Y', strtotime ($a_row['publishDate']));
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  4. #4
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    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. ";
    Last edited by Dasher; 12-24-2010 at 06:24 PM.

  5. #5
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,279
    Quote Originally Posted by Dasher View Post
    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.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  6. #6
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,279
    Quote Originally Posted by Dasher View Post
    MySQL handles the possible date change as wel
    As does PHP's strtotime().
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  8. #8
    Join Date
    Jul 2010
    Posts
    35

    Thanks

    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 Code:
    <?php echo date("F j, Y"$date_created); ?>
    Happy New Year!

    Mr. Baggins

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles