...

View Full Version : Pulling Data From Last Month



curb
03-14-2007, 11:45 PM
I can't seem to to make the query call out last months highest clicks. I read the INTERVAL and subdate but don't know how to apply it.

I'm trying to get the last month and not 30 days. Any help would be appreciated.


SETTING====
Fieldname: time
Type: int
UNIXTIME

Here's what I have for the query:


SELECT id, title, url, imgname, clicks
FROM students
WHERE approved = 'yes'
ORDER BY clicks DESC
LIMIT 8

Fumigator
03-15-2007, 01:26 AM
You can't use date_sub() function on an INT data type. Convert your field to a DATETIME data type and then you can use datetime functions on that field...



SELECT *
FROM table1
WHERE month(datefield1) = month(date_sub(datefield1) INTERVAL 1 month)

guelphdad
03-15-2007, 01:27 AM
how do you mean by last month and not 30 days. Do you mean February?

YOu will also have to use FROM_UNIXTIME if you are using a UNIX timestamp.

curb
03-15-2007, 01:50 AM
Yes, sorry, I mean by February. When I changed the INT into DATETIME, it reseted all my dates into 0000-00-00 00:00:00

Fumigator
03-15-2007, 04:44 AM
You need to convert the data, not just change the data type and pray. Guelphdad has a link in his sig how to do this.

curb
03-15-2007, 05:46 AM
Whew, I finally got it to work. Thanks!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum