Fetch data of last 6 month

sandy1028

Hi,

Please help me how to data in the table of last 6 months.
Example, from Octoberl 1 2007 to March 2008.

Excluding the current month.

Sxooter

OK, this is the basics, you can go from here

select * 
from table 
where 
    timestampfield between 
        '2007-10-01 00:00:00' AND
        '2008-03-31 23:59:59'

or something like that

sandy1028

Hi,

But how to find when month changes... It will become static value in the query

Sxooter

You can either use php to create the first and last date and stick them in the query, or use date math in your db to do the same. There are lots of examples in the MySQL and PostgreSQL docs on how to do it with the db, and some nice examples in the php manual of how to do it there.

Give it a shot, come back if you get stuck.

kakki

maybe this

SELECT ...
FROM ...
WHERE myDate>=DATE_SUB(NOW(),INTERVAL 6 MONTH)

Sxooter

kakki wrote:

maybe this

SELECT ...
FROM ...
WHERE myDate>=DATE_SUB(NOW(),INTERVAL 6 MONTH)

A step in the right direction, but he wants whole months... I'd use date_trunc in pgsql to do it.

select 
from
where datefield between 
    date_trunc('month',now()-interval '7 months')::timestamp and 
    date_trunc('month',now())::timestamp - interval '1 second';

Of course, mysql will have different built in functions...