SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2002
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question DATETIME column in MySQL

    Ok, I have got a table with a super MySQL DATETIME column ...

    Ok ... when I run an INSERT query what command should I tell MySQL in order to add Current Time and Date to this column ?


    CURDATE() and CURTIME() do exist but how can I take them together and have current date and time in that DATETIME column ?

    Should I use use php date and time ?


    Thanks,

    Andrea

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A query you could run is;

    Code:
    INSERT INTO table SET date_time_col = CURRENT_TIMESTAMP();
    There's a whole load more good stuff that MySQL will do for you described here and that's before you even get to PHP.

    Highly recommend this article on Date/Time Processing with PHP as well - there's some good tricks there.

    And finally, some useful functions here to make life easier.

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2002
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HHHEHEH, got it !!!!

    SYSDATE() returns everything ...

    "YYYY-MM-DD HH:MM:SS" if used as date/time

    or

    YYYMMDDHHMMSS if used as a number.



  4. #4
    SitePoint Zealot
    Join Date
    Mar 2002
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HarryF, I didn't see your post !!

    Just got it on SUPER Kev's book .... I didn't find it in MySQL online documentation ... just my fault for sure ...


    Thanks HarryF, HEY, you really LOVE ASP ????



  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    **Sigh** LOVE ASP? Err - explained here - note to self - never gamble again.

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2002
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MY GOD !!!




    I'm sorry about you ....



  7. #7
    SitePoint Member rIGO's Avatar
    Join Date
    Jan 2002
    Location
    Malta
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    formatting DATETIME

    ok i have a problem.

    i cant seem to format my datetime column in my mysql table.

    i would like to change the date from 2002-06-14 18:30:00 to MON 14th June 2002 6:30 pm.

    how can it be done...in my php script.

    rIGO
    rIGO

  8. #8
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Real quick.

    Head to http://www.zend.com/codex.php?CID=348 and find more PHP date / time converters than you can shake a an MCSE badge at!

    Otherwise there's a great tutorial on this subject right here.

  9. #9
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For setting date/time columns to the current time, I always use NOW()...

    Code:
    INSERT INTO table SET date_time_col = NOW()

  10. #10
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can format the date directly with MySQL using DATE_FORMAT();

    2002-06-14 18:30:00 to MON 14th June 2002 6:30 pm.

    Code:
    SELECT DATE_FORMAT(datecolumn, '%a %D %M %Y %H:%i %p') AS newdate FROM yourtable
    (untested)

    Link: http://www.mysql.com/doc/D/a/Date_and_time_functions.html

  11. #11
    SitePoint Member rIGO's Avatar
    Join Date
    Jan 2002
    Location
    Malta
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    im a beginner....didnt get it yet

    sorry guys....but im a beginner and im not getting it. i wish a very small and simple datetime conversion. not something complicated.

    i wish to use the DATE_FORMAT but not sure where to use it. could u pls show me where...i am pasting the code below.

    thanks for your help.

    Code:
    <?php
      
      // Request all the details of the fixtures
      $result  = @mysql_query("SELECT id, datetime, venue, competition, white, blue, home, away FROM fixtures ORDER BY datetime");
      if (!$result) {
        echo("<p>Error performing query: " . mysql_error() . "</p>");
        exit();
      }
    
      // Display the fixtures in a table
      echo "<TABLE BORDER=\"1\">\n";
      while ( $row = mysql_fetch_array($result) ) {
        $date  = $row["datetime"];
        $venue = $row["venue"];
        $comp  = $row["competition"];
        $white = $row["white"];
        $blue  = $row["blue"];
        $home  = $row["home"];
        $away  = $row["away"];
        echo "<TR><TD> " . $date . " </TD>\n";
        echo "<TD> " . $venue . " </TD>\n";
        echo "<TD> " . $comp . " </TD>\n";
        echo "<TD> " . $white . " </TD>\n";
        echo "<TD> " . $blue . " </TD>\n";
        echo "<TD align=\"center\"> " . $home . "-" . $away . "</TD></TR>\n";
    }
    echo "</TABLE>\n";
    ?>
    rIGO

  12. #12
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    funny, i had a comment on php.net for substr in which i wrote a function to do just what you want, but it's not there anymore...

    Well, here's the code. It just pulls the date, but it'd be fairly easy to grab the time as well
    PHP Code:
    function timestamp_convert($timestamp

    $year substr($timestamp04); 
    $month substr($timestamp42); 
    $day substr($timestamp62); 


    $new_date $year."-".$month."-".$day
    return 
    $newdate

    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  13. #13
    SitePoint Member rIGO's Avatar
    Join Date
    Jan 2002
    Location
    Malta
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but the column i have is not a timestamp... they are dates in the form YYYY-MM-DD HH:MM:SS. i want to know how to convert it to Mon 14th JAN 6:30 pm using PHP.

    the code i have got for now is the one above (last post). cause i cant seem to understand where to put all the codes everyone is telling me to do. im getting it wrong everytime.

    pls teach me...let me learn.

    thanks a lot
    rIGO

  14. #14
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Even as DATETIME you can still use my function, you just need to change the 2nd and 3rd values in the substr functions to reflect the positions of each part of the date/time.

    DATETIME stores in this format: YYYY-MM-DD HH:MM:SS

    So, 0,4 would be the year, 6,2 would be the month and so on...

    Once you've got the raw numbers, use mktime() to convert it to a unix timestamp.

    and from there it's a simple format with date()
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


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
  •