Web Analytics Made Easy -
StatCounter Date Format in MySQL - CodingForum

Announcement

Collapse
No announcement yet.

Date Format in MySQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

    Comment


    • #3
      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

      Comment


      • #4
        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.
        PHP Code:
        header('HTTP/1.1 420 Enhance Your Calm'); 
        Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

        Comment


        • #5
          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

          Comment


          • #6
            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.
            PHP Code:
            header('HTTP/1.1 420 Enhance Your Calm'); 
            Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

            Comment


            • #7
              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

              Comment


              • #8
                INSERT INTO table (CreatedOn) VALUES (NOW()).
                I would consider the written date to be the date provided to me.
                PHP Code:
                header('HTTP/1.1 420 Enhance Your Calm'); 
                Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Drop downs then.
                    PHP Code:
                    header('HTTP/1.1 420 Enhance Your Calm'); 
                    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        You should be sanitizing your data before it gets to your database.

                        Comment


                        • #13
                          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

                          Comment


                          • #14
                            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.
                            PHP Code:
                            header('HTTP/1.1 420 Enhance Your Calm'); 
                            Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

                            Comment


                            • #15
                              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.

                              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

                              Comment

                              Working...
                              X