1

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.

Community
  • 1
  • 1
notmyname
  • 69
  • 2
  • 6

1 Answers1

1

'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.

Ben English
  • 3,900
  • 2
  • 22
  • 32
  • Ben, thank you for the examples. What do you mean "between includes the bounds"?. – notmyname Feb 07 '12 at 15:53
  • BETWEEN is equivalent to `min <= x <= max` if x equals min or max it will be included so if you do `date BETWEEN '2011-09-09' AND '2011-09-10'` it will include records with a date = to midnight ('2011-09-10 00:00:00') on the 10th. – Ben English Feb 07 '12 at 16:52
  • I may need to open another question for this - If I am looking for a particular time that is equal to or between the time in the two fields, will that be different? So for instance I want 2011-09-09 01:00:00 and it falls between a start and stop time. Thanks for your time. – notmyname Feb 07 '12 at 17:43