Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142

    Unanswered: MySQL and PHP Date Format strategies?

    Hi, folks! I'm new to MySQL and PHP and working on my 1st project and have spent hours reading the PHP manual, MySQL manual, and some posts on what I thought was a simple subject and is not... dates. I know everyone's different, but I'd like to know how folks store/retrieve/display their dates and what they would recommend for my situation...

    MySQL table (kinda like an address book):
    creationdate, datetime, will initialize manually when record/row created
    modifieddate, timestamp, auto-updated everytime record/row modified
    firstname, text
    lastname, text
    ... 12 other fields

    WEB site (I'll simplify)...
    webpage1 = list of people (link to detail page... pass ID thru URL)
    webpage2 = detail (select * tablename where ID=$id)

    When I retrieve and display modifieddate, it is an unformatted string... 20040528142620

    So, I do not how to correct this to show in a more meaningful format. Should I....

    (1) select modifieddate a different way
    (2) create & store a new field/column that is equivalent to modifieddate but formatted as I want it so it is already ready-to-show and displayed the same as the other fields are selected and displayed
    (3) use PHP to convert string to timestamp and use PHP date()
    (4) use MySQL to convert to Unix_Time and use PHP date()
    (5) manually update modifieddate in a correctly formatted form whenever record/row modified

    Any tips/suggestions and explanations would be appreciated.
    Thanxalot!
    --ST

  2. #2
    Join Date
    Jan 2002
    Location
    Nottingham, UK
    Posts
    37

    datetime or timestamp?

    are you sure that data type is datetime? it looks like timestamp. I'd say do number 3, but leave out the step of converting it to timestamp cos it looks like it already is in that format.

    see
    http://dev.mysql.com/doc/mysql/en/Da...ime_types.html
    ----
    system:
    Mac Powerbook 1GHz

  3. #3
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Thanx, skinny! yeah, I think you're right... I had thought it was unix/epoch but I see it is timestamp. When I use date() on it, though, I get some weird date that I cannot identify from the string, even if I parse it differently.

    Anyone else? What do most folks do?
    --ST

  4. #4
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    $whenmodified=20040528142620;
    <? print ( date ("M d, y", $whenmodified) . " raw:" . $whenmodified); ?>

    outputs...
    Jan 18, 38 raw:20040528142620

    It seems I am using the date() function incorrectly since I should be getting something like
    May 28, 04

    Can anyone point out my obvious but not-so-obvious-to-me mistake? Thanxalot!
    --ST

  5. #5
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Hi, folks! Hmmm... I guess I was looking for a pre-defined function. A Google search led me to http://www.awtrey.com/support/dbeweb/php.php where I saw an example of something similar. I have to manually parse the string myself. Here's a clipping from the website cited...

    ----------------------------------

    Using the MySQL timestamp datatype in PHP requires a little coding. This next bit takes apart the MySQL data and generates a formated date. The substr function just grabs a substring from a given string. The mktime function generates a timestamp that the date function uses to generate the date and time in the format specified.

    <?

    ////////////////////////////////
    // The database has a timestamp record type that we can use to show the
    // date the guestbook was filled out.
    ////////////////////////////////
    $datefromdb = $guest['guest_time'];
    $year = substr($datefromdb,0,4);
    $mon = substr($datefromdb,4,2);
    $day = substr($datefromdb,6,2);
    $hour = substr($datefromdb,8,2);
    $min = substr($datefromdb,10,2);
    $sec = substr($datefromdb,12,2);
    $orgdate = date("l F dS, Y h:i A",mktime($hour,$min,$sec,$mon,$day,$year));
    ?>
    Date: <? echo $orgdate; ?>
    Last edited by Steve T.; 06-21-04 at 14:17.

  6. #6
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Here is my finished code from my include file. If you are a newbie and trying to use/display/output mysql timestamp dates/times, I hope this helps...

    <?

    /* ************************************************** **********************
    whenmodifiedconversion.inc
    INCLUDE: TIMESTAMP DATE CONVERTER

    This file parses a mysql timestamp, $whenmodified, into usable substrings.
    The substrings are then used w/the php function mktime() to convert the
    date to a Unix epoch time integer. Lastly, the php date() function is used
    to assign appropriate values to $whenmodified_date and $whenmodified_time.

    I should probably have made this some kind of re-usable function/method/class
    but since this is my 1st php/mysql project, I do not know how to do that yet.
    --ST
    ************************************************** ************************* */

    $year = substr ($whenmodified, 0, 4);
    $month = substr ($whenmodified, 4, 2);
    $day = substr ($whenmodified, 6, 2);
    $hour = substr ($whenmodified, 8, 2);
    $min = substr ($whenmodified, 10, 2);
    $sec = substr ($whenmodified, 12, 2);

    $whenmodified_epochtime = mktime($hour, $min, $sec, $month, $day, $year);
    $whenmodified_date = date ("m/d/Y", $whenmodified_epochtime);
    $whenmodified_time = date ("g:i:s a", $whenmodified_epochtime);

    ?>

  7. #7
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    P.S. I guess I should add that a large part of my confusion had to do w/ the term "timestamp". In MySQL terminology, timestamp seems to mean something like yyyymmddhhmmss, whereas in PHP, timestamp seems to refer to a Unix Epoch Time integer.

    Shheezz! And I thought it'd be simple.
    --ST

Posting Permissions

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