Date/ Time Question?

[Solarpitch]

Hey Guys,

 

I have an input field that takes in the date in the format of "07/22/2009". In the database where I want to store this, the field is datetime. However, when I try to save this to the DB it stores it as "0000-00-00 00:00:00".

 

Just wondering, do I have to convert this to a different timestamp or something? Just not 100% sure. Then if I need to read it from the DB agai.. do I ned to re-convert it. How id this generally handled?

[ignace]

It's better to use timestamp's as they allow you to convert it to any format you want (remember to change timezone offsets on your db-server or web-server if they are not on the same machine or even in the same country).

[PFMaBiSmAd]

The conversion to/from a Unix Timestamp is both somewhat slow and error prone (depends on the time zone setting at the time of each conversion and on how up to date your DST database is.) On the other hand, a DATE or DATETIME entered into a database will always have the correct value it was entered as and can be formatted any way you want. Formatting a DATE or DATETIME is about 8-10 times faster (use the mysql DATE_FORMAT() function in your SELECT query) than the conversion a Unix Timestamp needs to get it into a usable format.

 

I have an input field that takes in the date in the format of "07/22/2009"

Getting a human to correctly enter a date in the correct format every time is not going to happen. It is actually better if you provide three drop-down select menus or a javascript date-picker so that they can only pick dates it the correct format.

 

the field is datetime

If you are only storing the date, just use a DATE data type, not a DATETIME.

 

The format for a DATE field is YYYY-MM-DD. Attempting to put a value into a DATE field that is not in the correct format results in the minimum value defined for that field. To get your "07/22/2009" format into a YYYY-MM-DD format, just explode it on the '/' and arrange the parts in the format you need them -

 

$yourdate = "07/22/2009";
list($month,$day,$year) = explode('/',$yourdate);
if(checkdate($month,$day,$year)){
echo "Valid date<br />";
$date = "$year-$month-$day";
echo $date;
} else {
echo "Invalid date<br />";
}

 

If you have already validated the incoming date, a second method to get the mm/dd/yyyy format into a YYYY-MM-DD is to use the mysql STR_TO_DATE() function directly in your INSERT query.

[ignace]

The conversion to/from a Unix Timestamp is both somewhat slow and error prone (depends on the time zone setting at the time of each conversion and on how up to date your DST database is.) On the other hand, a DATE or DATETIME entered into a database will always have the correct value it was entered as and can be formatted any way you want. Formatting a DATE or DATETIME is about 8-10 times faster (use the mysql DATE_FORMAT() function in your SELECT query) than the conversion a Unix Timestamp needs to get it into a usable format.

 

Didn't knew that, thank you