Getting last months data from the database

Question

My query currently gets yesterdays data but I now want to get all of last months data. (E.g.from 1st to 31st)

$res = mysql_query("SELECT
FROM_UNIXTIME( enquiry_time ), `id` ,
`fullname` , `address1` , `citytown` ,
`postcode` , `telno` ,
`property_value` ,`total_secured_debt`
, `email` ,  `on_market` , `agent` , 
`reason` ,  `price_concession` ,
`asking_price` , `form_page` FROM
$table WHERE TO_DAYS(FROM_UNIXTIME( enquiry_time )) = (TO_DAYS(NOW())-1)
");

There is not a TO_MONTHS so im not sure where to start!

Answer 1

Looking for function MONTH?

That will work (like your TO_DAYS does now), but remember you may be paying a hefty performance price if the table is large: MySQL can't use an index when the WHERE condition depends on a function rather than directly on the indexed column (use EXPLAIN to see the query plan and confirm that this is the case). Performance-wise, you're better off computing the lower and upper bound of the times you want to see (with functions and arithmetic on NOW()) and then using a straight BETWEEN in your WHERE. Again, use EXPLAIN to double check -- trying to second guess MySQL's query optimizer is a sterile exercise, it's much easier to check on what it plans to do;-).

Answer 2

Try:

$res = mysql_query("
    SELECT FROM_UNIXTIME(`enquiry_time`), `id`, `fullname`, `address1`,
        `citytown`, `postcode`, `telno`, `property_value`, `total_secured_debt`,
        `email`, `on_market`, `agent`, `reason`, `price_concession`,
        `asking_price`, `form_page`
    FROM $table
    WHERE YEAR(FROM_UNIXTIME(`enquiry_time`)) = YEAR(CURDATE() - INTERVAL 1 MONTH)
    AND MONTH(FROM_UNIXTIME(`enquiry_time`)) = MONTH(CURDATE() - INTERVAL 1 MONTH)
");

This would all work vastly better and faster if enquiry_time were a native DATE or DATETIME field, with an index, instead of a Unix timestamp.

Answer 3

Could you use something like the following? I think it will get you what you want.

FROM_UNIXTIME(enquiry_time) 
BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') 
AND last_day(NOW() - INTERVAL 1 MONTH)

Here's the reference.

Answer 4

While chaos's answer will work, if $table is indexed by enquiry_time, then it will be faster to compare against that field directly than against a function of it. That transforms his answer into the even uglier:

$res = mysql_query("
    SELECT FROM_UNIXTIME(enquiry_time), id, fullname, address1,
        citytown, postcode, telno, property_value, total_secured_debt,
        email, on_market, agent, reason, price_concession, asking_price,
        form_page
    FROM $table
    WHERE enquiry_time >= UNIX_TIMESTAMP(concat(year(curdate() - interval 1 month), '-', month(curdate() - interval 1 month), '-01'))
    AND enquiry_time < UNIX_TIMESTAMP(concat(year(curdate()), '-', month(curdate()), '-01'))
");

Answer 5

i see you are using php so this might be better than wrestling with mysql functions:

$end_of_month = strtotime(date('Y-m-01 00:00:00'))-1;
$start_of_month = strtotime(date('Y-m-01 00:00:00',$end_of_month));

$res = mysql_query("SELECT
FROM_UNIXTIME( enquiry_time ), `id` ,
`fullname` , `address1` , `citytown` ,
`postcode` , `telno` ,
`property_value` ,`total_secured_debt`
, `email` ,  `on_market` , `agent` , 
`reason` ,  `price_concession` ,
`asking_price` , `form_page` FROM'
$table WHERE enquiry_time >= '".$start_of_month."' AND enquiry_time <= '".$end_of_month."'
");