...

View Full Version : Date Format in MySQL



doubledee
08-21-2011, 08:17 PM
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
08-21-2011, 09:19 PM
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
08-21-2011, 09:22 PM
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
08-21-2011, 09:29 PM
Yes it is, that is why you need to convert it.

I should note that the datetime is a data type. What you see is how its been formatted.

doubledee
08-21-2011, 09:39 PM
Yes it is, that is why you need to convert it.

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
08-21-2011, 09:48 PM
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
08-21-2011, 10:01 PM
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
08-21-2011, 10:08 PM
INSERT INTO table (CreatedOn) VALUES (NOW()).
I would consider the written date to be the date provided to me.

doubledee
08-21-2011, 10:19 PM
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
08-21-2011, 10:20 PM
Drop downs then.

doubledee
08-21-2011, 10:25 PM
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
08-21-2011, 10:27 PM
You should be sanitizing your data before it gets to your database.

doubledee
08-21-2011, 10:36 PM
You should be sanitizing your data before it gets to your database.

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


Debbie

Fou-Lu
08-21-2011, 10:39 PM
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
08-21-2011, 10:52 PM
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

Fou-Lu
08-21-2011, 11:07 PM
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

No its not.
Simple explainations:
Verified: Ensuring that the data is the proper type (including not empty / null unless appropriate).
Validated: Ensuring that the data meets business rules (ie: date not in the future).
Sanitized: Ensuring that the data cannot corrupt another language when marshalled.

You are asking about CONVERTING format. That is all.

And as I have said, you can accept ANY format that you want. Its your job to make sure its provided to the database in a way that it understands. You may use a drop down, a text entry, binary numbers, checkboxes, strings, numbers, radio buttons, spinners, or whatever you want to do. You just need to convert it so that SQL will accept it.

guelphdad
08-22-2011, 12:55 AM
Debbie, before posting in forums (this one or other ones) at a MINIMUM please read the manual. I know you don't because on a recent thread in another forum you tell us you don't. Straightforward stuff from the manual where you know where to look (such as date and time types or character sets) should be looked up.

You can see from the manual what format datetime accepts.
You can also find out how to use STR_TO_DATE to format the data.

From that point you can decide whether or not you can format the datetime from the front end or back end. If you can do it in either place then you can decide where to do it.

Sanitizing data before input means you handle that in the front end. That was in reference to dates of Feb 29th perhaps being created. The database will accept that date, it may not accept Feb 30th, but that is up to you, the programmer to decide how to clean user input before it goes anywhere near the database.

doubledee
08-22-2011, 01:58 AM
No its not.
Simple explainations:
Verified: Ensuring that the data is the proper type (including not empty / null unless appropriate).
Validated: Ensuring that the data meets business rules (ie: date not in the future).
Sanitized: Ensuring that the data cannot corrupt another language when marshalled.

You are asking about CONVERTING format. That is all.

And as I have said, you can accept ANY format that you want. Its your job to make sure its provided to the database in a way that it understands. You may use a drop down, a text entry, binary numbers, checkboxes, strings, numbers, radio buttons, spinners, or whatever you want to do. You just need to convert it so that SQL will accept it.

If someone types...


2011-08-21'; DROP ARTICLE whatever;

...that could cause an issue.

I initialized asked about what was a valid format so I would know what data validation and sanitizing I might have to do.

And I asked here, because while anyone can read the manual until death, I like the perspective of people who do this for a living and can give me practical advice.

Isn't that why most people post here? If everyone just looked things up "in da manual", then CodingForums - and all the ad revenue people like me generate - would dwindle away to nothing...

I'm not trying to be difficult, but I'm a newbie and require more guidance than most on here.



Debbie



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum