Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: DateTime in mysql

    Hello all,

    I insert into my database using php a variable date which i get from php's date() function. This is inserted into the database. When i echo the contents i get this:

    2030-08-07 07:31:00 (example)

    What is the extra 20 for, every row contains this 20 infront of the date? Is this something mysql does on purpose?!

    Thanks all, i appreciate any help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it means you have entered the dates incorrectly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    it means you have entered the dates incorrectly

    I don't think thats the case - since this is what i did:

    PHP Code:
    <?php

    $subject 
    $_POST['title'];
    $text $_POST['msgpost'];
    $date date('d-m-y:h-i');

    $query sprintf("INSERT into threa (subject, text, date) values ('$subject', '$text', '$date')") ;
    mysql_query($query) or die(mysql_error());

    ?>
    Is there anything wrong with this that could cause a entry into the database like this: 2030-08-07 07:31:00 for the date field.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i don't do php, but i can read it, and maybe even modify it
    Code:
    <?php 
    
    $subject = $_POST['title']; 
    $text = $_POST['msgpost']; 
    
    $query = sprintf("INSERT into threa (subject, text, date) values ('$subject', '$text', current_timestamp ) ") ; 
    mysql_query($query) or die(mysql_error()); 
    
    ?>
    feel the force, luke

    dates must be specified in year-month-day sequence -- you had day-month-year sequence, and you just happened to use values which could be interpreted as year-month-day, except that the year was assumed to be in this centry, which is why the 20 got prepended

    it's much easier (and more efficient) just to tell the database to use the current timestamp, rather than trying to construct that string yourself -- for one thing, the database first has to parse your date string and try to figure it out, which takes extra processing

    passing in a date string is not wrong if you have to do it, but when it's really just the current timestamp you want, that's what that special SQL keyword was invented for

    neat, eh?
    Last edited by r937; 09-05-07 at 17:42.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    feel the force, luke

    neat, eh?
    Hell yes, thank you luke

    It is much more efficient, less likely for an error to occur since were dealing directly with the sql.

    I really need to find a list of sql statements before i start writing duplicate scripts!!

    EDIT: for the date field, it is a DATETIME type, this is ok right, it will give me back the date and time(hours:minutes hopefully)

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Probably reading the manual section for what you are trying to use would help answer a lot of your questions - http://dev.mysql.com/doc/refman/5.0/en/datetime.html

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by dbmab
    Probably reading the manual section for what you are trying to use would help answer a lot of your questions - http://dev.mysql.com/doc/refman/5.0/en/datetime.html
    Good point - its just that there are so many words.

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I suspect the PHP code you were looking for is :

    Code:
    $date = date('Y-m-d h:i:s');
    Might also be worth noting that you can input dates into your DB datetime column just using YYYY-MM-DD , and the hours mins secs will be zeros (00) .
    So if you're just doing this use NOW() instead of CURRENT_TIMESTAMP. Also if you want to put in the date and time now + 1 hour, then you can do
    DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 HOUR)

    Hope these help.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    So if you're just doing this use NOW() instead of CURRENT_TIMESTAMP.
    no, don't

    first of all, they are the same thing, and secondly, CURRENT_TIMESTAMP is the standard SQL keyword, NOW() is proprietary mysql syntax

    and if you want the date only, with no time or with 00:00:00 as the time, use CURRENT_DATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Aaah thats probably what I was looking for. I thought NOW() was date only? Checking MySQL manual now :P

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    it's much easier (and more efficient) just to tell the database to use the current timestamp, rather than trying to construct that string yourself -- for one thing, the database first has to parse your date string and try to figure it out, which takes extra processing
    I've just realised. When i need the date and time from the database i need it in the form: dd-mm-yyyy hh-mm but the database seems to put the year first which will mean i have to do some work with php to re-arrange it. So it might be better just to create the string and input it into the db. Either way, php will have to be involved.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the mysql DATE_FORMAT function was invented for just such a requirement

    so neither way is php involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    the mysql DATE_FORMAT function was invented for just such a requirement

    so neither way is php involved
    I really need to read thanks again r937

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    ehhh...am i using the CURRENT_TIMESTAMP correctly:

    PHP Code:
    $query sprintf("INSERT into $table (subject, content, date) values ('$subject', '$text', 'CURRENT_TIMESTAMP')") ;
    mysql_query($query) or die(mysql_error()); 
    Since i just got an error that i have never seen before?!

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(subject, content, date) values ('test', 'test ' at line 1

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    smells like a php error

    try your query outside of php to be sure



    p.s. do not put CURRENT_TIMESTAMP in quotes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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