Professional Web Applications Themes

PHP, MySQL and datetime - PHP Development

I'm building an application for scheduling employees in a department. For the datetime-related dtaa , is their a "best practice" for the data storage? For example, in building a schedule table, I need the date portion for the column headers (days of the week) and I need the hour portion for the rows. I am currently storing datetime data as mm-dd-yyy hh:mm:ss. I seem to be always having trouble gettign the date to p correctly and to build the correct SELECT statements to retrieve the right data. Is it better to just store the data in a unix-style format? I'm ...

  1. #1

    Default PHP, MySQL and datetime



    I'm building an application for scheduling employees in a department.
    For the datetime-related dtaa , is their a "best practice" for the data
    storage? For example, in building a schedule table, I need the date
    portion for the column headers (days of the week) and I need the hour
    portion for the rows. I am currently storing datetime data as mm-dd-yyy
    hh:mm:ss. I seem to be always having trouble gettign the date to p
    correctly and to build the correct SELECT statements to retrieve the
    right data. Is it better to just store the data in a unix-style format?
    I'm not really asking for how-to hints as the app is coming along nicely
    but it seems inelegant and I was wondering if there are some helpful
    hints when handling date and time
    Joe Guest

  2. #2

    Default Re: PHP, MySQL and datetime

    Joe wrote: 

    Joe -

    I would say use the mysql data type of Datetime [
    http://dev.mysql.com/doc/mysql/en/datetime.html ] . It stores the data
    as YYYY-MM-DD HH:MM:SS. From there you can use the DATE_FORMAT and
    TIME_FORMAT functions [
    http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html ] to
    select different parts of the one Datetime column as multiple rows.
    You can let mysql do all the parsing and formatting for you, so php is
    just echoing the data, or whatever you need to do with it.

    you could say
    SELECT DATE_FORMAT(DateTimeCol, '%b %e %Y') AS DateOutput,
    DATE_FORMAT(DateTimeCol, '%a') as DayOutput, TIME_FORMAT(DateTimeCol,
    '%T') AS TimeOutput FROM Table

    The query will return 3 rows, DateOutput, DayOutput, and TimeOutput
    DateOutput -> Jan 01, 2005
    DayOutput -> Sun
    TimeOutput -> 12:00:00 [ which is hh:mm:ss ]

    Hope that helps.

    -peter
    peter Guest

  3. #3

    Default Re: PHP, MySQL and datetime

    Joe wrote: 

    Yes, use the MySQL DATETIME format, and associated functions.

    http://dev.mysql.com/doc/mysql/en/datetime.html
    http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html


    --
    Oli
    Oli Guest

  4. #4

    Default Re: PHP, MySQL and datetime

    In article <qDFae.2796$yc.2000trnddc02>, com
    says... 



    Thanks - that does look like an easier way of handling it from the
    database side. I typically will put things like DayOutput in an array
    then to lay it out in a table:
    print "<tr>";
    foreach($DayOutput as $i => $DayName)
    {
    print "<td>$DayName"</td>";
    }

    print "</tr>";

    Is that the best solution?
    Joe Guest

  5. #5

    Default Re: PHP, MySQL and datetime

    Joe wrote: 
    >
    >
    >
    >
    > Thanks - that does look like an easier way of handling it from the
    > database side. I typically will put things like DayOutput in an array
    > then to lay it out in a table:
    > print "<tr>";
    > foreach($DayOutput as $i => $DayName)
    > {
    > print "<td>$DayName"</td>";
    > }
    >
    > print "</tr>";
    >
    > Is that the best solution?[/ref]

    I will usually have something like

    $sql = "SELECT DATE_FORMAT(DateTimeCol, '%b %e %Y') AS DateOutput,
    DATE_FORMAT(DateTimeCol, '%a') as DayOutput, TIME_FORMAT(DateTimeCol,
    '%T') AS TimeOutput FROM Table";
    $results = mysql_query($sql);

    echo "<table>";
    while ( $data = mysql_fetch_assoc($results) ) {
    echo "<tr><td>{$data["DateOutput"]}</td></tr>";
    echo "<tr><td>{$data["DayOutput"]}</td></tr>";
    echo "<tr><td>{$data["TimeOutput"]}</td></tr>";
    }
    echo "</table>";

    PHP has a great many mysql functions. [ http://us2.php.net/mysql ].
    One of them mysql_fetch_assoc, returns the query data in an associative
    array. That is the array's index is the Row's Name and the value is the
    Row's data.

    -peter
    peter Guest

  6. #6

    Default Re: PHP, MySQL and datetime

    Hey Joe,

    I've built a few calendar/scheduling applications in the past. I have
    messed around with different formats while learning and concluded the
    most usefull format to store it is a unixtimestamp... (mktime(); in
    php). Easiest to subtract add and so on... I usually store it as just an
    integer type in mysql databases. With the date and all other functions
    php has for handling dates, it is not very difficult to convert that
    into any format that you need it displayed. Mysql itself has some pretty
    good date parsing functions that you might want to look into. It all
    depends on how you want to distribute the server load.. (if ur web/db
    servers are different.)

    Hope that helps,

    Paul

    Joe wrote: 
    Paul Guest

Similar Threads

  1. question about datetime column in mysql
    By nephish in forum MySQL
    Replies: 2
    Last Post: October 30th, 01:41 PM
  2. Replies: 2
    Last Post: March 7th, 07:18 PM
  3. Translating MySQL timestamp to datetime
    By Joshua Beall in forum PHP Development
    Replies: 1
    Last Post: April 22nd, 02:50 PM
  4. MySQL datetime extraction
    By Yasir Malik in forum PHP Development
    Replies: 1
    Last Post: July 19th, 07:27 PM
  5. [PHP] MySQL datetime extraction
    By David Nicholson in forum PHP Development
    Replies: 2
    Last Post: July 19th, 06:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139