Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Date Format in MySQL

    Is it correct that the DATETIME format in MySQL stores data as:

    "yyyy-mm-dd hh:mm:ss"


    Is there a way to store the DateTime a different way?

    Or do I leave it as-is and then just format it differently in PHP during output?

    And what about on Input...

    Can a user type "3/15/2011 9:27pm" and have it end up as "yyyy-mm-dd hh:mm:ss"?



    Debbie

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    No, that doesn't match the format of a datetime object in mysql. Similar to strtotime you can convert it with STR_TO_DATE: STR_TO_DATE('03/15/2011 9:27pm', '%c/%e/%Y %l:%i%p') providing it with the desired input format.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    No, that doesn't match the format of a datetime object in mysql. Similar to strtotime you can convert it with STR_TO_DATE: STR_TO_DATE('03/15/2011 9:27pm', '%c/%e/%Y %l:%i%p') providing it with the desired input format.
    "yyyy-mm-dd hh:mm:ss" is not the format that MySQL stores DATETIME in?


    Debbie

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Yes it is, that is why you need to convert it.
    Edit:
    I should note that the datetime is a data type. What you see is how its been formatted.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Yes it is, that is why you need to convert it.
    Edit:
    I should note that the datetime is a data type. What you see is how its been formatted.
    So if someone enters

    "yyyy-mm-dd hh:mm:ss"

    into my date field then everything is okay on the bac-end with MySQL, but if the user enters a different date/time format, then MySQL wouldn't accept it, correct?

    And so the way I handle that is to either force the user to enter a Date/Time into the format that MySQL is expecting (e.g. using Drop-Downs) or to use some function to convert a different Date/Time format into the one above that MySQL is expecting, correct?


    Debbie

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    That's more or less correct. MySQL will attempt several cast types to see if it can fit it, but it must always be year then month then day.
    There is no magical answer to date and time handling. There are far too many possible formats for datetime entering for a language to decisively use especially when altering possible formats that could become ambiguous: 12/11/10 for example; what is the year, the month and the date?
    Fortunately SQL is one of the best; you can tell it what format it will expect it to be in instead of leaving it up to the language to decide what rule has to be met.

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    That's more or less correct. MySQL will attempt several cast types to see if it can fit it, but it must always be year then month then day.
    There is no magical answer to date and time handling. There are far too many possible formats for datetime entering for a language to decisively use especially when altering possible formats that could become ambiguous: 12/11/10 for example; what is the year, the month and the date?
    Fortunately SQL is one of the best; you can tell it what format it will expect it to be in instead of leaving it up to the language to decide what rule has to be met.
    So if you had a form "Add an Article" and had a form field called "Written On", how would you handle getting the date from the form into the database safely?

    Any approaches that work best for you?

    (I guess I'm leaning towards getting rid of a fee-form Text Box and instead using Drop-Down Lists for a non-JavaScript solution.)


    Debbie

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    INSERT INTO table (CreatedOn) VALUES (NOW()).
    I would consider the written date to be the date provided to me.

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    INSERT INTO table (CreatedOn) VALUES (NOW()).
    I would consider the written date to be the date provided to me.
    Nope.

    created_on = NOW()

    written_on = when I create the article on my laptop earlier this summer...

    So, since that date needs to be manually entered into my form, what approach would you favor?

    Free-form with a function to clean up on the tail-end?

    Drop-downs?

    Other?


    Debbie

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Drop downs then.

  • #11
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Drop downs then.
    Okay.

    But then I'd need to use checkdate() as well to eliminate things like "February 29, 2001", right??

    Thanks,


    Debbie

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You should be sanitizing your data before it gets to your database.

  • #13
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    You should be sanitizing your data before it gets to your database.
    Right, that is what this entire thread is about...


    Debbie

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by doubledee View Post
    Right, that is what this entire thread is about...


    Debbie
    Um, no its not. This entire thread is about inserting an alternate datetime format into a datetime datatype. There has been no discussion on verifying, validating, or sanitizing the input.
    You can use PHP's checkdate() prior to insertion and build the string to match MySQL's expected datetime format.

  • #15
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Um, no its not. This entire thread is about inserting an alternate datetime format into a datetime datatype. There has been no discussion on verifying, validating, or sanitizing the input.
    You can use PHP's checkdate() prior to insertion and build the string to match MySQL's expected datetime format.

    Quote Originally Posted by doubledee
    Is there a way to store the DateTime a different way?

    Or do I leave it as-is and then just format it differently in PHP during output?
    So in other words, if the Date/Time entered into my form has to be in a certain format because it can only be in one format in the back-end database, then what are my options to get it into an "acceptable" (i.e. "sanitized") format...

    That is what we have been talking about all along...

    That is why I asked about Date/Time validation functions and about form designs like drop-downs.

    Hello...


    Debbie


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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