Date Format in MySQL

[doubledee]

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

[Fou-Lu]

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.

[doubledee]

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

[Fou-Lu]

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.

[doubledee]

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

[Fou-Lu]

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.

[doubledee]

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

[Fou-Lu]

INSERT INTO table (CreatedOn) VALUES (NOW()).
I would consider the written date to be the date provided to me.

[doubledee]

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

[Fou-Lu]

Drop downs then.

[doubledee]

Drop downs then.

Okay.

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

Thanks,


Debbie

[guelphdad]

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

[doubledee]

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

Right, that is what this entire thread is about...


Debbie

[Fou-Lu]

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.

[doubledee]

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.

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