Processing dates for use in your application?

How do you guys go about processing dates for use in PHP and then storing in the database.

For example a user is performing a search for events that have taken place past 19-12-2010.

What would you normally do in order to use this date in your application?

Hi, I would normally use MySQL ‘date’ column which takes dates in the following format:

YYYY-MM-DD

YMD allows for simple date checking (since 2010 > 2009 will always be true.) Simple checks (> , <, =, combinations thereof) will rely on this formatting, which is why MySQL’s date uses that format. Also why it’s datetime uses Y-M-D H:m:s in 24 hour mode for hour.

More on above two replies, if your user input is on that dd-mm-yyyy format then you have to convert them to regular format (yyyy-mm-dd) first then use it for the database purpose.

Letting users pick dates is usually the least error prone way of doing things when starting out.

It is easy to generate basic drop down lists which are OK as long as you check that when you reassemble the parts of the date you run the PHP function [fphp]checkdate/fphp over it first.

Relying on JS calendar pickers might be another solution, but still, its up to you to check the incoming date is a real date before going any further.

Dealing with JS cal pickers invariably means [fphp]explode/fphp ing the value and reassembling it for Mysql, and of course vice-versa when you want to display it.

The introduced in PHP 5.2 then improved in 5.3 dateTime class can help a lot too.

http://www.php.net/manual/en/datetime.format.php

thanks for the replies guys, I am currently using the same method as Cups and Rajug however was wondering if there was a more standard way.

Would it be considered “bad practice” to just strtotime() what the user enters, then check the output of that?

Here is how I deal with dates:

  1. All dates are stored as signed integers.
  2. All date formats for the ui are predefined (i.e. DD.MM.YYYY)
  3. Upon receiving the date: explode to parts, use check_date() to find if it is valid
  4. Convert to time with mktime()
  5. Do stuff with database records

Another benefit is that when you display data you can easily do date “math”. For example
find the difference of 2 dates. And by only changing the timezone you can display
local time for the users without doing anything else.

I believe that is the standard way for most.

If you can constrain the user to submit day - month the correct way round then yes, that can work. The dateTime object encapsulates the same behaviour as strtotime() too.

This is usually the problem in the UK where users expect to submit dd/mm/yyyy.

using strtotime() will be problematic when faced with dates like 01/02/2011 - is the 1st of Feb or 2nd of Jan?

Why dont you tell us what your gui is permitting users to send back?

Well I am going to use a javascript date picker, therefore it would be in the form dd/mm/yyyy however it doesn’t stop people going back into the input field and modifying it.

Thanks again guys.

In that case, if it is impossible to pick an illegal date (31st Feb, say) the submission of an illegal dates means your user is modifying data, and you should perhaps:

Log this behaviour
Throw them out of your system if logged in
Return them to your home page

So your task will becoime:

explode the incoming dates on slashes (/)
reassemble them for checkdate()
if that passes reassemble them for your database query

Like so:


// change to a bad date to test 319/12/2010
$incoming = "19/12/2010";

$date = explode("/", $incoming);

if(!checkdate($date[1], $date[0], $date[2] )){

echo 'outa here';
exit();

}

$qry = "select stuff from mytable where dateField > '$date[2]-$date[1]-$date[0]'";

echo $qry ;

If the datepicker can let the user to pick the right format date (time) then why don’t you make the date field read only so that they cannot edit/modify anything directly in the field.

A determined user can spoof any data by making their own version of your form, regardless of what you do in html/JS. You have to treat everything incoming as suspicious.

Agreed! But I was only concerning that what makes easier on how to check the date. I am not saying that making the field read only is only solution.