anybody good with querying dates?

[dadamssg87]

I have events stored in my database with start(datetime) and end(datetime). I want to query a range of two dates and count how many events fall between the range.

 

I have no idea where to begin constructing this kind of query. Any help would be awesome.

 

[Pikachu2000]

SELECT COUNT(field) AS cnt FROM table WHERE DATE(datetime_field) BETWEEN 'YYYY-MM-DD_start_date' AND 'YYYY-MM-DD_end_date'

[dadamssg87]

thanks for the quick response. i'm a little confused where i put the start and end date of the range i want to query.

 

I'm not sure what 'field' would be in "COUNT(field)", if i obviously want to count the entire row. Also 'datetime_field'?

 

For instance let's say i have the following

<?php
$input_start = "2011-05-02";
$input_end = "2011-05-07";
$table_name = "Bookings";

 

my table structure is as follows

start 	datetime
end datetime
id int(11)
name varchar(100)

 

 

 

SELECT COUNT(field) AS cnt FROM table WHERE DATE(datetime_field) BETWEEN 'YYYY-MM-DD_start_date' AND 'YYYY-MM-DD_end_date'

[dadamssg87]

i thought i had it with this but it won't catch a booking that starts on the the day the date range ends

 

SELECT *
FROM `Bookings`
WHERE END >= '2011-05-02'
AND START <= '2011-05-07'

[dadamssg87]

Think i figure it out with this

 

SELECT *
FROM `Bookings`
WHERE END >= '$input_start'
AND START <= '$input_end'
UNION
SELECT * FROM `Bookings`
WHERE date(start) = '$input_end'

[gizmola]

Well yes this is one of those issues where you have a DATE time so when you specify a date by itself, it gets turned into the equivalent of midnight on that day.  You can rectify that by having the dates be '2011-05-02 23:59:59' or you can change those to be > and

[gizmola]

Just for the record, you don't have to do anything to the > date, because it's already going to be the first second of that day.