Querying database for dates and times help?

Databases
1

Hi,

I want to query times in my database, but i’m stuck. So for example I have details of a meeting which:

Starts: 2010-12-21 09:00:00
Ends: 2010-12-21 10:00:00

Saved into my database.

So what I want to do is run a query which checks a SET half an hour time period which is between ’ 2010-12-21 09:00:00’ and ’ 2010-12-21 09:30:00’ which the above period falls into, but i’m pretty stuck as the query i’m working on below won’t work as the ending period is at 10am, even though 9.30 is between 9 and 10​?:


SELECT * FROM `TBL` WHERE start_time >= '2010-12-21 09:00:00' AND ends <= '2010-12-21 09:30:00' AND user_id = 3

Anyone kindly help?

Thanks

2 

WHERE start_time < '2010-12-21 09:30:00'
AND ends > '2010-12-21 09:00:00'
3

Thanks, but that won’t work as it would get all entries before that timeframe in my database and all entries after.

What I need is check a time period ‘2010-12-21 09:30:00’

and output al records that cover this period, so say I have the columns as:

Start: 2010-12-21 09:00:00
End: 2010-12-21 09:29:00
would not output any data

Start: 2010-12-21 09:00:00
End: 2010-12-21 19:29:00
would output rows

Hope that makes sense.

Thanks

4

You should decide what you want: a 30 minute period or a single time. :slight_smile:
But for the solution it makes little difference. Did you try my where conditions? Or did you just decide that by the looks of it it won’t work?