[RESOLVED] Please critique this query

richie

OK, so I want to know how many records have been updated in the last minute.
Here is what I came up with...

select count(0) from mytable
WHERE
unix_timestamp(date_field) BETWEEN unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute)) AND unix_timestamp(now())

My date field is named "date_field", and it's in datetime format.

Any obvious problem with this query?

bradgrafelman

If you're using a MyISAM table, COUNT(*) is optimized to count the number of rows in the result set, IIRC.

Also, why are you converting everything into Unix timestamps?? Just do the date arithmetic in-line without any conversions:

WHERE date_field BETWEEN NOW() AND NOW() - INTERVAL 1 MINUTE

or an alternate method:

WHERE date_field >= NOW() - INTERVAL 1 MINUTE

EDIT: Also, note that my entire post assumes you're using MySQL.

richie

bradgrafelman, thanks for your reply.

I was converting to unix_timestamp because I wasn't sure how mysql deals with date strings.

I ultimately went with your simpler solution:

WHERE date_field >= NOW() - INTERVAL 1 MINUTE

bradgrafelman

For reference, the following excerpt is from the MySQL manual page for the DATE_ADD() function:

MySQL 5.1 Manual wrote:
Date arithmetic also can be performed using INTERVAL together with the + or - operator:

date + INTERVAL expr unit
date - INTERVAL expr unit

INTERVAL expr unit is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr unit is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

richie

mysql's galaxy of date functions will make your head spin. The possibilities seem endless on ways to solve a particular date-related problem.

I don't know about other databases, but mysql rocks with dates .