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

    Join Date
    Mar 2000
    Location
    Hong Kong
    Posts
    73
    Rep Power
    15
    Umm...I have no idea on how to store the date/time in MySQL database.

    Anyone got a sample code which deal with time/date in PHP/MySQL?

    Indeed, I am making a tiny guest book like program. I want to store/retreive time and date in/from MySQL database.

  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bochum, NRW, Germany
    Posts
    13
    Rep Power
    0
    You need a special field in your table from the variable type DATETIME.
    You can fill this with an extra MYSQL function named NOW() like this:

    mysql_query("insert into table values (..., NOW(''),...");

    The function NOW() insert the actual date/time, but you can even save every date/time by entering manually.

    The data is stored in the format: YYYY-DD-MM HH:MM:SS

    Check any MySQL tutorial for info.

    Hope that will help you...

    Martin
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Hong Kong
    Posts
    73
    Rep Power
    15
    Thanks Martin.
    now() works fine for me.
    I just wonder if I can convert the format
    of the date/time
    i stored my date/time in the format of
    YYYY-MM-DD HH:MM:SS

    I want to show it in this format:
    MM-DD-YYYY HH:MM in PHP

    anyone know how to do it?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    select date_format(date_column, '%m %d %Y %l:%i %p) as When from table_name;

    // %p gives AM/PM if you need it

    Check out MySQL's date and time functions on the online manual for more formatting options (military time, ordinal suffixes, a bunch of stuff)
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Hong Kong
    Posts
    24
    Rep Power
    0
    Thanks Kyuzo. (This is 'ccm'. I am using another account in office.)
    Indeed, I am a newbie in both PHP and MySQL.
    I have already created a table with a column named 'date datetime'.
    And I have used NOW() in PHP to store the date/time info in MySQL database.
    For now, I retreive the date/time info by '$row->date' in PHP.
    I have no idea what to do next.
    Do I have to re-create the whole table?
    And how do I implement the Date/Time format in my table?
  10. #6
  11. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Hi hkccm,

    You don't have to recreate the table for changing a field type.

    u may try this..

    'alter table tblname change old_field new_field TIMESTAMP(-);'

    for getting a special date format you can use
    TIMESTAMP--with in bracket pass value for a specific format.

    See the mysql documentation for the correct syntax.



  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bochum, NRW, Germany
    Posts
    13
    Rep Power
    0
    To change the date/time format you can even use the regular expression stuff, it's quite mighty!

    Example:
    $date = "2000-03-13";
    ereg("([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})",$date, $regs);
    echo "$regs[3].$regs[2].$regs[1]";

    Output: 13.03.2000

    This function takes the date ($date) and splits it into an array ($regs) by odering after YYYY ($regs[1]), MM($regs[2]), DD($regs[3]) - there was a failure in my first posting ;-)).
    You can now easily change the order by changing the order of the array.

    Martin

Similar Threads

  1. Time field ? Time AND date ?
    By oobiExtant in forum MySQL Help
    Replies: 7
    Last Post: December 28th, 2004, 10:11 AM
  2. mysql date time stamp and UTF8
    By doomish in forum MySQL Help
    Replies: 0
    Last Post: February 8th, 2004, 09:37 AM
  3. WANTED: PHP/mySQL programmer in SF bay area
    By saturn5 in forum Hire A Programmer
    Replies: 0
    Last Post: January 16th, 2004, 11:14 PM
  4. how do I filter the time field in oracle
    By tjtee in forum Oracle Development
    Replies: 1
    Last Post: January 12th, 2004, 10:56 AM
  5. Replies: 0
    Last Post: January 5th, 2004, 08:44 AM

IMN logo majestic logo threadwatch logo seochat tools logo