Thread: Date Woes

  1. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    Struggling a bit with the date command.

    I have a MySQL timestamp field called "answers_date" with the value "19990912154608"

    So why does this:

    echo (date("F j, Y", $myrow["answers_date"]));

    yield this:

    January 18, 2038?

    Thanks in advance...
  2. #2
  3. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    The problem is that your timestamp value is a string, and the date() function expects an integer value. You should use the UNIX_TIMESTAMP() function when returning the answers_date value using an alias, like this:

    select UNIX_TIMESTAMP(timestamp_col) as answers_date from my_table;

    timestamp_col is the column that contains the timestamp. If the column name is answers_date, then make up a new name to follow 'as' and use that in your php script.
  4. #3
  5. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    This appears to have worked like a
    charm, and I appreciate the help.

    Trying to find some sort of help with this
    from the PHP docs proved impossible, and
    the mySQL manual presents all sorts of ways
    to turn their goofy timestamp to a normal unix timestamp, but implementing this in PHP turned out to be intellectually challenging.

    Thanks again.
  6. #4
  7. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    Using a mySQL field as timestamp -- this is my PHP code to query the timestamp using mySQL "UNIX_TIMESTAMP" -- is there a better way than doing two different selects here? This code works fine, but I'd like it to be as effecient as possible:

    $result = mysql_query("SELECT * FROM questions where (quest_id=$quest_id)",$db); ## $quest_id is primary key - its the question we're grabbing

    $second = mysql_query ("SELECT UNIX_TIMESTAMP(quest_date) as $date from questions where (quest_id=$quest_id)",$db);

    ## Above, I've already queried this table, and the timestamp value would be in $myrow["quest_date"] per below, but above I query again to get in the UNIX_TIMESTAMP form. This works, but why must I query twice? Is there a way to grab "quest_date" in the first query as UNIX_TIMESTAMP (therefore making second query above no longer needed??)

    ## the rest of the code goes on to do this:

    if ($myrow = mysql_fetch_array($result)) {
    ## ... blah blah blah, then prints date like this:

    echo date ("F j, Y", mktime ($date));

    Like I said - this works fine, but seems awfully messy.

    Help and comments appreciated.

    I am using timestamp because I need the date to be automatically entered when the row is updated.

Similar Threads

  1. database date selection problem
    By kidsleep in forum MySQL Help
    Replies: 0
    Last Post: January 31st, 2004, 10:50 AM
  2. date addition problem
    By strange1 in forum PHP Development
    Replies: 3
    Last Post: October 6th, 2003, 04:01 PM
  3. Javascript date validation: advance to next valid date
    By TruckStuff in forum JavaScript Development
    Replies: 2
    Last Post: September 19th, 2003, 11:21 AM
  4. HTML Form not passing date value
    By XyberCom in forum PHP Development
    Replies: 14
    Last Post: September 14th, 2003, 01:18 PM
  5. MIME mail headers (date)
    By aidanpeiser in forum PHP Development
    Replies: 0
    Last Post: May 23rd, 2003, 04:07 AM

IMN logo majestic logo threadwatch logo seochat tools logo