Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Confused about date formats between PHP and MySQL

Posted on 2009-07-06
8
Medium Priority
?
201 Views
Last Modified: 2012-05-07
I've been wrestling with this most of the day and have finally decided to ask for help. I have a form with two dates, a "from" date and a "to" date.

I've got the form set up to accept dates in either mm/dd/yyyy or mm-dd-yyyy format. That's all working fine. So basically I have my dates as strings with the month first, then the day, then the year.

How do I now use these dates in a MySQL query? Using as is just doesn't work. Probably because MySQL wants to see the dates as yyyy-mm-dd, right? Do I actually have to convert them to that format? I did that and it does seem to work, but it also feels "klugey" and I wonder if there is some better way?

Also, after I converted the dates to yyyy-mm-dd strings to use in the where clause, I still have a problem because, for example, my "to" date is 2009-05-21, and there is a date/time in the database that is 2009-05-21 11:14. When I do a "less than or equal to" on the "to" date, it doesn't include this date, probably because what it's using is a time of 00:00.

What's the right way to be doing these date comparisons? All I want to do is have the user put in a start and end date, and then query the database to include all records between the two dates, inclusive. So 06/01/2009 to 06/30/2009 would give everything in June.

And on another note, just a simple quick question: do you use != for not equal, or !== ? It seems I've used both and both seem to work.

Thanks.
0
Comment
Question by:alicia1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:mallcore
ID: 24789933
Are your times in the table as unix timestamp?
0
 
LVL 12

Accepted Solution

by:
kevin_u earned 1000 total points
ID: 24789942



The simplest way to do date ranges is to add:

23:59:59 to end date strings when you pass it to the mysql query.

So if the post shows:
2009/06/30 ... it becomes 2009/06/30 23:59:59

There are many other ways.

Generally speaking you have to deal with this stuff in an application specific way.

Making both the user and php happy is somtimes a task.

0
 

Author Comment

by:alicia1234
ID: 24789953
No they are "datetime".

I "kluged" the "to" date by adding " 23:59:59" to the end of it, and that now works correctly, but, again, it's not very "elegant". ;-)

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Expert Comment

by:kevin_u
ID: 24789960
any other solutions generally require more code.
0
 

Author Comment

by:alicia1234
ID: 24789985
So my basic approach is ok, given that the dates are "datetime" in MySQL? My code is below. I didn't include the functions here but you'll get the gist. The code does work.

The query is:

$query =  "SELECT * FROM tblSurveys WHERE ( (surveydate >= '{$startdate}') && (surveydate <= '{$enddate}') ) ORDER BY surveydate DESC";  

if (	isset($_POST['startdate']) && $_POST['startdate'] != '') {
			if (isDate($_POST['startdate'])) {
				$startdate = datestring_to_MySQL($_POST['startdate']);
			} else {	
			$message = ERR_INVALID_DATE; 
			}
		} else {
			$startdate = '1950-01-01'; //a long time ago!
		}

Open in new window

0
 

Author Comment

by:alicia1234
ID: 24789998
oops - my code got cut off ... here it is again:

if (isset($_POST['startdate']) && $_POST['startdate'] != '') {
if (isDate($_POST['startdate'])) {
$startdate = datestring_to_MySQL($_POST['startdate']);
} else {	
$message = ERR_INVALID_DATE; 
}
} else {
$startdate = '1950-01-01'; //a long time ago!
}
 
if (isset($_POST['enddate']) && $_POST['enddate'] != '') {
if (isDate($_POST['enddate'])) {
$enddate = datestring_to_MySQL($_POST['enddate']);
$enddate .= ' 23:59:59'; 
} else {	
$message = ERR_INVALID_DATE; 
}
} else {
$enddate = '2099-12-31'; //a long time in the future
}

Open in new window

0
 

Author Comment

by:alicia1234
ID: 24790012
kevinu: While you were posting the 23:59:59 "kluge", I was doing just that. But good to hear that it's kind of the "accepted" way to do it.
0
 
LVL 12

Expert Comment

by:kevin_u
ID: 24790041
Well, its my accepted way.... there may be arguments to the contrary.

You could store using the date data type instead of datetime.

There are combinations of functions (either php string stuff or date manipulation in php) that would allow you to store only the date (truncate the time) before inserting the data in the first place.

There combinations of functions to pull only the date from a datetime field (involves converting to a string, and truncating the string)

This may provide more insight.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question