I'm using MySql 5.5,
I am using the following query on a radius accounting table.
query1:
SELECT * FROM database.table WHERE framedipaddress='192.168.1.1' and '2011-09-09' BETWEEN acctstarttime AND acctstoptime;
The acctstarttime and acctstoptime fields are type datetime, formatted as YEAR-MT-DY HR:MN:SC.
Using query2 reveals my results of query1 are not always accurate, query1 sometimes will return nothing or an incorrect row.
query2:
SELECT * FROM database.table WHERE framedipaddress = '192.168.1.1' ORDER BY acctstarttime DESC LIMIT 0, 400;
Is there a different method to query two datetime, fields, or is it the database sanity/data is the issue?
Any guidance to the errors in my logic are welcome.
Here is the question for the query I have now.
'2011-09-09' = '2011-09-09 00:00:00' So you are only querying accounts that had an acctstarttime before this time AND an acctstoptime after this time.
If you're looking for accounts that were started on 2011-09-09. Then you could do
...
WHERE
acctstarttime >= '2011-09-09' AND acctstarttime < '2011-09-10'
If you're looking for accounts that were started and ended on 2011-09-09. Then do:
...
WHERE
acctstarttime >= '2011-09-09' AND acctstarttime < '2011-09-10' AND
acctstoptime >= '2011-09-09' AND acctstoptime < '2011-09-10' AND
FYI if you use BETWEEN it includes the bounds so it would include 2011-09-10 00:00:00.