DateTime MySQL query?

Anyone know of any simple tutorials, links or how to perform
a MySQL query with PHP on the DateTime field in the database?

MySQL Database has DateTime Values (Example: 2/9/2006 3:30:06PM)

Want to be able to perform the following queries:

1: specific date (show all records on that date)

2. range of dates (show all records in between 2 dates. example: form
1/1/2006 to 1/3/2006)

Thanks!!!

 

//select specific date
$sql = "SELECT * FROM my_table WHERE YEAR(date_field) = '2006' AND MONTH(date_field) = '9' AND DAY(date_field) = '11'";

PHP:

Not sure how to do the range off the top of my head, but I'll post if I remember

 

SELECT * FROM table WHERE date > '".$from_date."' AND < '".$to_date."'

A little tip

Run an If statement

If ($to_date){

$query = "SELECT * FROM table WHERE date > '".$from_date."' AND < '".$to_date."';
}else{
$query = "SELECT * FROM table WHERE date = '".$from_date."'";
}
$result = mysql_query($query);

 

jestep's code seems like it should work, but I tend not to use functions in the where clause since they often don't use the index and might not perform as well.

onlyican's example seems good, but be careful about:

SELECT * FROM table WHERE date = '".$from_date."'";

Since you're using datetime, you'll probably have to always do range queries to find all the records from a specific date. If you do a comparison with "=" on today's date '20060308000000', there probably aren't exact matches for the time part. What you probably want is:

$query = "SELECT * FROM table WHERE date >= '20060308000000' AND < '20060309000000';

It's trickier than it sounds since if you only have the date, you have to create a range by adding 1 day to the start date. So if you wanted to get info from a form and then form a query it would be something like:

$month = $_REQUEST['month'];
$day = $_REQUEST['day'];
$year = $_REQUEST['year'];

// You should probably do some error checking on the above values here...

$php_start_time = mktime(0,0,0, $month, $day, $year);
$php_end_time = $php_start_time + (24 * 60 * 60); // Add 1 day to start date.
$start_date = date('Y-m-d', $php_start_time) . '000000';
$end_date = date('Y-m-d', $php_end_time) . '000000';

$query = "SELECT * FROM table WHERE date > '".$start_date."' AND < '".$end_date."';

I haven't actually tested this code, but hopefully it gives you an idea of the tricky areas. For guidance, I usually go straight to the php.net and mysql.com manuals. The reference manuals often have comments that are helpful.

 

thanks for all the help ..

I ended up coming accross something really simple ...

Can it get easier then that? It works!

One thing I did need to add though was

to take into consideration if calculating records from 1 day.
thx!!!

 

I have a similar question, but none of the posted suggestions work.

At a prototype site I have a mySQL query on range of dates that works perfectly. In the user form, you can specify any combination of "from" and "to" dates in the format YYYY-MM-DD and either by year only, year-month, month or day. I have the exact duplicate of this query at another site (with the database connection info modified, of course) and it does not work. I am wondering if the following might be the problem:

Working location: MySQL version 4.1.18-standard-log
nonWorking location: MySQL version 4.0.18-standard

Here's the query:

$query = "select * FROM Enhancements WHERE Integrated between '".$var1."' AND '".$var2."' ORDER by Sys";

I would much appreciate any suggestions.

Michael

 

Try your query with all lowercase letters for the table and field names.

Some earlier versions dont support UpperCase Field and Table Names.

If you add this statement under the mysql_query($query);

echo mysql_error();

It will tell you what the problem is.

 

Anybody get anywhere with this? I'm having the same issue with a query on one of my sites.

 

I´m beggining with php and mysql and I would like to know about a query where I can get all the days of the month except weekends !