Search code examples
mysqlunix-timestampstrtotimeconcat-ws

How to limit recordset to dates in the past

Second try on this, I just don't get it.

I want to remove graduated students whose grad dates are passed vs $today so I want to limit the recordset to only those whose dates in the db are in the past.

My table stores gradMonth and gradYear as varchar, i.e. June 2013.

So, $today = strtotime(date("Y-F")). That works and produces 1378008000 as expected.

I want to concatenate the table field values of gradMonth,gradYear and compare them to $today. So my query is:

SELECT 
    gradYear, gradMonth 
FROM 
    myTable 
WHERE 
    UNIX_TIMESTAMP(CONCAT_WS('-', gradYear, gradMonth, '01')) < '$today'

Oddly, this is consistently including records for October 2014 and other months not smaller than $today, and I don't know why.

Thanks for any ideas or help you can give.

Solution

  • A more MySQL native approach:

    WHERE DATE(CONCAT_WS('-', gradYear, gradMonth, '01')) < ?

    Supplying a parameter of strtotime(date("Y-m-d") for that placeholder.

    If you convert your gradYear and gradMonth to a proper DATE column, this query would look like:

    WHERE gradDate < ?

    I'm not sure how "Y-F" as a format ever worked since that produces things like "2013-January" which isn't going to sort properly, as "April" would then be the first month of the year ASCII-abetically speaking.