Jump to content


Photo

Working with TIME


  • Please log in to reply
5 replies to this topic

#1 phppup

phppup
  • Members
  • PipPipPip
  • Advanced Member
  • 286 posts

Posted 03 May 2018 - 01:44 AM

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.

#2 requinix

requinix
  • Administrators
  • Impoverished Administrator
  • 9,874 posts
  • LocationWA

Posted 03 May 2018 - 05:26 AM

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.
"Basically, I think the general rule of thumb is: if someone really wants the blood that's inside of your body, and they're like a vampire, or a dracula, or some sort of man-squito, then that's probably okay. A dracula and a man-squito are made for removing things like blood and swords from inside your body. That's basically fine. If something wants to get at your blood and they're, say, some kind of murdersaurus, or maybe a really big frog, that's where the problems start to arise. A really big frog is not made for removing blood, and your blood knows this, which is why it is so vehement about wanting to stay in your body instead of coming out. Unfortunately this will not deter a really big frog because a really big frog is full of things like prizes, and value, and quite a lot of hatred, and it would really rather like to replace any and all of those things with your blood, and basically by any means possible." --slumbermancer

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,169 posts

Posted 03 May 2018 - 09:59 AM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 phppup

phppup
  • Members
  • PipPipPip
  • Advanced Member
  • 286 posts

Posted 03 May 2018 - 11:38 AM

@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.

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,169 posts

Posted 03 May 2018 - 12:00 PM

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).


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,722 posts
  • LocationLos Angeles, CA USA

Posted 03 May 2018 - 03:32 PM

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.c...different..html
http://www.gizmola.c...ySQL-Dates.html
http://www.gizmola.c...ySQL-query.html
http://www.gizmola.c...-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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users