Get all Months before current month?

[aesthetics1]

This may sound a little complicated.. I'm fairly new to some of these concepts so please excuse me if there is a much easier way...

 

Basically, I am looking to show all entries from my mysql database that have a timestamp earlier than the current month. The entries are in the following format (where the first number is the year, the second is the month, the third is the day):

 

2010-07-23

 

Basically, I want to show all entries that have:

 

2010-06-XX

2010-05-XX

2010-04-XX

2010-03-XX

2010-02-XX

2010-01-XX

 

What would the syntax of the SQL query for something like that look like? What PHP code would I need to grab this? I already have code grabbing the current month, and searching for anything that looks like:

 

'%-$current_month-%'

 

Like I said, please excuse me if this is a rather strange way of going about this.

 

Thanks for any help.

 

 

EDIT: I am currently looking at http://php.net/manual/en/function.date.php to see if there is anything helpful here but I haven't found anything quite suited to my situation...

[jcbones]

Is your database column a date or timestamp column?

 

If so.

 

SELECT * FROM table WHERE MONTH(datecolumn) < MONTH(NOW()) AND YEAR(datecolumn) = YEAR(NOW());

[gizmola]

So the field in question is a mysql TIMESTAMP column?  You can construct what you need using the MySQL

 

Read my ancient blog entry on this topic:  http://www.gizmola.com/blog/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html so you understand the underlying principles of why this works.

 

Let's say your timestamp column is named 'createdDate'

 

SELECT * from yourTbl WHERE createdDate 

[aesthetics1]

It is a timestamp column actually. Will it work if all entries are followed by 00:00:00? how about if the field is NULL for some entries?

 

I think that might work for me, going to test it out... Thanks for the speedy reply!

 

EDIT:

The fields are actually varchar, go figure.. Trying to convert them over right now but I'm getting an error:

 

#1292 - Incorrect datetime value: '' for column 'columnname' at row 7

 

looks like it doesn't like an empty value somewhere?..

[gizmola]

Nulls, might not work, you will have to test, but you can also add in an AND ISNULL for those if they should be included.  If there is a date, but the time element is all 00:00:00 that's fine.  Again, read my blog entry to understand why. 

[aesthetics1]

I do not want the NULLs included in what is grabbed from the database, so that is fine. I don't think I am going to be able to convert the structure of the field over in its current state though, can't figure out why. All values are either a timestamp format (2010-07-23 00:00:00) or 'NULL...'

[gizmola]

Yeah looks like something is wrong with that date, so it can't convert it.  You may instead have to convert the values using PHP and its date handling functions, although this will be very poor performance wise, because you can't use mysql's indexes and just get back the rows you want.  You're best off if you can clean the data and insure from now on that it's a valid DATE or DATETIME or TIMESTAMP column.

[aesthetics1]

Alright, I have found where the problem lies in converting the tables over. There are actually a few dirty entries among the 2000 or so rows. A few of them lack the 00:00:00, and a few were blank, and not NULL.

 

Is there a method for cleaning off the 00:00:00 from all of the rows so I can just fix the blank ones and convert it to just a date?

[gizmola]

Sure, use UPDATE statements to clean up the ones that are garbage.  You can use SQL to find the offenders and fix them prior to conversion.  Look at the mysql string handling functions.

 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

 

Functions like substr and locate should probably be enough to do the job.

[aesthetics1]

Thank you very much for your help! That worked perfectly, I'm getting exactly what I needed.

[gizmola]

Glad we helped.  If you get a chance you can click on the bottom and mark your topic as Solved using the Green Mark Solved button.