Working with TIME

[phppup]

I am planning to use a JQuery datepicker as an easy user interface on a form.

 

This will identify a "start date" that the user will select for his 5 day work shift. The data will go into a table and other calculations will use this start date going forward.

 

What is the most effective way to handle and manipulate this data for this situation? Is there a benefit or best practice?

 

Is it better to format date information using JavaScript on the client side, or with PHP?

 

Should I insert a date as mm-dd-yyyy or yyyy-mm-dd, or something else. Does it even matter?

 

Any helpful hints and opinions to point me in the right direction would be great.

[requinix]

What is the most effective way to handle and manipulate this data for this situation? Is there a benefit or best practice?

"Most effective way" to do what, exactly?

 

Is it better to format date information using JavaScript on the client side, or with PHP?

Whichever is most convenient.

 

Should I insert a date as mm-dd-yyyy or yyyy-mm-dd, or something else. Does it even matter?

It should be stored in the database as a DATE, as in the actual data type that is meant to be used for dates. I shouldn't have to explain why.

YYYY-MM-DD is the most portable format so go with that.

[Barand]

I would have the datepicker display the more user-friendly mm-dd-yyyy format.

 

In the PHP form processing, convert to yyyy-mm-dd for data storage

$pickerdate = '05-03-2018';

$dbdate = DateTime::createFromFormat('m-d-Y', $pickerdate)->format('Y-m-d');

echo $dbdate;              //--> 2018-05-03

[phppup]

@Requinix:"Most effective way" to do what, exactly?

To work with TIME in general. It seems JS requires manipulations from milliseconds in order to adopt user friendly information. PHP/MySQL appear to be able to accommodate 'real dates', so I avoid an added headache. I'd this a fair assessment?

 

"I shouldn't have to explain why"

Perhaps not. But would it hurt if you did?

Obviously I'm on the right track because I made the comparison. Èqually obvious is my lack of clarity on the subject because I asked the question.

 

I created my screen name to indicate that I was new and (while eager to grow) still bumping into walls and making (what the more experienced would consider) silly mistakes. The screen name had nothing to do with my affection of canines.

 

"Informative and sensative responses will educate and empower the website capabilities while reducing recycled post volume." Me.

[Barand]

mm-dd-yyyy and dd-mm-yyyy formats cannot be sorted into the correct sequence. This makes them useless for any date comparisons too.

 

Unix style integer time values can be sorted but are a pain to interpret when browsing your data and have limited range of values..

 

None of the above can be used by the mysql datetime functions without prior conversion, thus reducing efficiency.

 

Which leaves yyyy-mm-dd DATE types (or DATETIME or TIMESTAMP if the time element is required).

[gizmola]

To be clear, you need to differentiate between DATE, TIME and DATETIME. They are not the same things, nor universal, although there is some fluidity between them. Your post is titled "Working with Time" but the specifics of your question only involves Dates.

 

I've written a number of blog posts over the years about the related MySQL types. You might find these of interest:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

http://www.gizmola.com/blog/archives/archives/99-Finding-Next-Monday-using-MySQL-Dates.html

http://www.gizmola.com/blog/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html

http://www.gizmola.com/blog/archives/archives/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

 

You should strive in general to always use the smallest (in storage size) mysql data type you can, that will provide the functionality you need. If you are working strictly with dates, then you want to use a MySQL DATE type, that has no time component. If you look this up in the manual you will find that a single DATE stored requires 3 bytes.

 

As already stated by Barand, the best way to handle the transition between your javascript widget and your database storage is to have the Javascript widget pass the date to your MySQL code in a format that is acceptable to MySQL without conversion. And that format is the 'yyyy-mm-dd' format.