#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    65
    Rep Power
    15
    I'm writing a simple message board for my homepage. I have the mySQL table set up with a TIMESTAMP column for every message that is entered into it. So far, I can retrieve the date and format it the way i want - but It's always "Monday, January 18, 2038 - 10:14:07 PM" mo matter what time the message is entered. Any ideas on how to fix this?
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    Normally MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format.

    But i don't know how it is displaying in "Monday, January 18, 2038 - 10:14:07 PM" format...

    do one thing, just change this format in php using date() or mktime() functions.

    First get this date value from database and change that in php.


    see the following article for getting some good tips about date manipulation..
    http://www.phpbuilder.com/columns/akent20000610.php3




    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Shiju, I think you missed the point. It IS formatted the way he wants, it's just the value that's incorrect.

    FC, have you checked to make sure that the values of the timestamp field are correct by checking the output from the command line without formatting?

    If that's ok, post the code you are using the retrieve the data and output to the client.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    65
    Rep Power
    15
    I checked the table for the data in the timestamp colun for a messge i put in last night. It's "20000708025644" which i belive is the equivalent of 7/8/2000 at 2:56:44 AM.

    so here's how it retrieves:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $hola1 = mysql_query("select * from messages where isreplyto = '0' ORDER BY id");
    $getinfo = mysql_query("select * from messuser where username = '$username'");
    if ($polly = mysql_fetch_array($hola1)) {
    $num = mysql_num_rows($hola1);
    if ($cracker = mysql_fetch_array($getinfo)) {
    echo "<center><font face=arial><h2>Welcome to FerretMan.com's Message Board.</h2>";
    echo "<p></center>There are currently $num Messages on the Board.</p>";
    echo "<p align=left>Current topics:</p>";
    echo "<table border=1 cellspacing=1 cellpadding=2 width=100%>";
    echo "<tr><td bgcolor=#FAFCD1 width=40%><font color=#0000FF><b>Message Title:</td><td bgcolor=#FAFCD1 width=30%><font color=#0000FF><b>User Posting:<td bgcolor=#FAFCD1 width=30%><font color=#0000FF><b>Date Posted:</td></tr>";
    do {
    printf ("<tr><td bgcolor=#FAFCD1><font color=#0000FF><a href=readmess.php3?id=" .$polly["id"]. ">%s</a></td><td bgcolor=#FAFCD1><font color=#0000FF><a href="mailto:" .$cracker["email"]. "">%s</td><td bgcolor=#FAFCD1><font color=#0000FF>%s</td></td>n",$polly["subject"],$polly["sender"],$polly["date"]);
    }while($polly = mysql_fetch_array($hola1));

    echo "</table>";


    }
    echo "did this";
    }
    echo "did that";
    [/code]

    thanks all
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I don't see how you are getting the formatted date that you describe. There is nothing in the code you've given that formats the date. From what you've posted it should just output YYYYMMDDHHMMSS
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    65
    Rep Power
    15
    Sorry.. I must have left it out.. here's the date formating stuff..

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $datefix = date("l, F d, Y - g:i:s A",$polly["date"]);
    printf ("<tr><td bgcolor=#FAFCD1><font color=#0000FF><a href=readmess.php3?id=" .$polly["id"]. ">%s</a></td><td bgcolor=#FAFCD1><font color=#0000FF><a href="mailto:" .$cracker["email"]. "">%s</td><td bgcolor=#FAFCD1><font color=#0000FF>%s</td></td>n",$polly["subject"],$polly["sender"],$datefix);
    [/code]

    And the code i posted does return YYYYMMDDHHMMSS, and seems to be the right timestamp...

    Thanks,
    Ben
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    OK, I thought that might be it. The PHP date() function formats a unix-timestamp which is expressed as seconds since the unix epoch (Jan 1, 1970). To do what you want you can do one of 3 things.

    1. Change the field that you store the timestamp in to a bigint and store the unix timestamp in it.

    2. Convert the field to a unix timestamp when retrieved with the mysql unix_timestamp() function:

    select *,unix_timestamp(date_field) as untime from ....

    3. Use the mysql date_format function to format the date for you:

    select *,date_format(date_field,'%W, %M %d, %Y - %r') as fdate ...



    [This message has been edited by rod k (edited July 09, 2000).]
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    65
    Rep Power
    15
    Thanks rod k, it works perfectly now. I had mysql format it fo me, and it works wonderfully.

    Thanks again,
    Ben

Similar Threads

  1. Problems formatting a UNIX timestamp from MySQL
    By nathanks in forum PHP Development
    Replies: 3
    Last Post: August 22nd, 2003, 04:49 PM
  2. formatting URLs and timestamp within <form> input field
    By lentil dal in forum PHP Development
    Replies: 4
    Last Post: January 15th, 2002, 08:43 PM
  3. timestamp formatting with string?? how?
    By how-do-i in forum MySQL Help
    Replies: 1
    Last Post: October 23rd, 2001, 08:18 AM
  4. incorrect sorting of Unix timestamp
    By vladiator in forum MySQL Help
    Replies: 2
    Last Post: October 12th, 2001, 03:42 PM
  5. Pulling a timestamp and formatting it
    By mstembri in forum PHP Development
    Replies: 5
    Last Post: November 11th, 2000, 05:35 AM

IMN logo majestic logo threadwatch logo seochat tools logo