Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Datetime Default

    I have a database table where I have set the properties of one field to default to datetime and not null. The date I receive is 12/4/2007 with no time. Do I need to do something else? Thanks.

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    Quote Originally Posted by exdter
    I have a database table where I have set the properties of one field to default to datetime and not null. The date I receive is 12/4/2007 with no time. Do I need to do something else? Thanks.

    To insert the date into the MySQL database it should be formatted in the expected YYYY-MM-DD format.

    You have not specified if the date you receive is from a file or if a user has typed it into a webpage form field.

    If a user has typed it into a webpage form field, you should 1) use javascript to validate that the date entered is valid, and in the correct format, then 2) change the formatting before inserting it into the MySQL database.

    By way of an example, here are PHP solutions for the UK date format (it would be simple to change these to work for the US date format):


    PHP Code:
    function format_date_for_database($date_str) {

        
    // takes a date in format DD/MM/YYYY and returns it in ISO format (YYYY-MM-DD)

        
    if($date_str=="") {
            return(
    "");
        } else if( 
    ereg("([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})"$date_str$regs) ) {
            return 
    "$regs[3]-".str_pad($regs[2], 2"0"STR_PAD_LEFT)."-".str_pad($regs[1], 2"0"STR_PAD_LEFT);
        } else {
            return(
    "-1");
        }



    PHP Code:
    function format_date_for_screen($date_str) {

        
    // takes a date in ISO format (YYYY-MM-DD) and returns it in DD/MM/YYYY format

        
    if($date_str=="0000-00-00") {
            return(
    "");
        } else if(
    ereg ("([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})"$date_str$regs)) {
            return 
    "$regs[3]/$regs[2]/$regs[1]";
        } else {
            return(
    "-1");
        }



  3. #3
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Howdy... I think datetime is yyyy-mm-dd hh:mm:ss as yellowmarker mentions, so I just read/write MySQL db info that way and use PHP to get what I want. I'm new to both PHP and MySQL so don't know if the way I do it as the same as what was posted, but I parse the datetime into its component parts using substr() and then use mktime() function to convert it to unix epoch time. Once it is in unix epoch time, I can use php's date() function to get just about any format I want.. even using words instead of just digits if I want. Here is my code for converting my MySQL timestamp-14 field that I read into my php variable $whenmodified...

    $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);

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    steve, the mysql DATE_FORMAT function can do all of that in one line

    exdter, are you sure you're using mysql? because mysql does not allow you to set a default using a function, and furthermore, it will accept a date string like '12/4/2007' but it will interpret it as april 20, 2012
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    DATE_FORMAT
    Good to know, thanks. Since what I work on is web-based, I just find it easier to do it in PHP. Right now, I'm still in the learning stage and just getting data in/out of MySQL as PHP $variables... Plus, I usually just grab all of a record's fields in one simple SELECT command. I'd have to compose another $sql command string just for that field if I wanted to do that in SQL and then have to have another SQL statement if I wanted it some other format. Converting the timestamp to unix epoch gives me the flexibility to show it in just about any format using date() on the fly.

    Again, I'm just a newb, though, so I may change techniques as I become more experienced, and I'm sure there will be times ahead when I'm going to want to use DATE_FORMAT, so thanks again.
    --ST

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    Thanks for everyone's reply. I had my field set as datetime default and it was working. The datetime does not come from a web application. When the user clicks on submit on a web app, the date and time is supposed to insert automatically when the info gets uploaded into the MySQL table with the current date and time as default. Does this make sense?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case the web app is supplying the date and time, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    You can do it as rudy guesses you are doing and manually insert the current date and time from your application, but if you want the field automatically updated whenever the record is modified, you can use a timestamp field instead. Timestamp varies a little depending upon MySQL version, but my guess is that if you change your datetime field to a timestamp field, it'll work the way you want.

    Hope this helps!
    --ST

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but be aware, there is a big difference between a default value that is defined in the CREATE TABLE statement (and which you are allowed to define as the value of a function such as CURRENT_DATE in every database system except mysql) and the mysql TIMESTAMP datatype which updates itself every time the row is touched
    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
  •