mysql month()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

mysql month()

Post by xisle »

Looking for the most efficient mysql query for month comparison -
I am pulling from tables with minimum 200k records and performing multiple queries for building site useage reports.

Do you know a more efficient way to write this query?

Code: Select all

$query = "SELECT COUNT(DISTINCT user_id) as monthusers FROM Activity WHERE MONTH(last_visit)=MONTH(NOW())";
thanks in advance..
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

One possible caveat with the approach you've taken - won't that, for example, pick all last_visits from November regardless of the year the visit is made in?

Mac
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

aye good point, that isn't going to work.. I am ultimately trying to get away from 'last_visit LIKE '{$YearMonth}%'
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

Looks like I can avoid a 'LIKE' with a date_format(). Let me know if there are any other ideas..

Code: Select all

$query = "SELECT COUNT(DISTINCT user_id) as monthusers 
FROM Activity 
WHERE DATE_FORMAT(last_visit, '%Y%m')='{$YearMonth}'";
Post Reply