0

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.

Vahid Hallaji
  • 7,159
  • 5
  • 42
  • 51
AllThisOnAnACER
  • 331
  • 1
  • 4
  • 18
  • Thank you for the readable edit. – AllThisOnAnACER Sep 16 '13 at 20:43
  • MySQL is much happier if have a native `DATE` column. This query you have here cannot be indexed and will require a row-scan every time which is brutally slow for non-trivial datasets. – tadman Sep 16 '13 at 20:53
  • tadman - The db is set up as YYYY-MM-DD for DATE columns, and we don't ask for specific grad dates, just month and year. If I must change this, now is the time to do it, but does my point make sense? This is an administrative page only, not for outside users. – AllThisOnAnACER Sep 16 '13 at 21:00
  • Well, just pick an arbitrary day, like `1` or close enough if you're only concerned with the month and year anyway. Having separate columns is almost always annoying, especially because MySQL can do computation and validation on the native columns that's not easy with two otherwise unrelated integers. – tadman Sep 16 '13 at 21:18

2 Answers2

1

You could do this:

SELECT gradYear, gradMonth FROM myTable WHERE date_format(CONCAT(gradYear,'-',gradMonth,'-01 00:00:00'),'%Y-%m') < date_format(CURRENT_TIMESTAMP(),'%Y-%m')
Mino
  • 911
  • 1
  • 7
  • 14
1

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Y-F in strtotime produced 2013-September. Okay, I'm going to take your advice, convert to DATE and make some adjustment. I'll get back to you and credit up when I get it working. Thanks for your help. I'll be in touch, (especially if it doesn't work :-) ) – AllThisOnAnACER Sep 16 '13 at 21:41
  • Generally English names for dates are a bad idea. The [ISO 8601 format](https://xkcd.com/1179/) is the preferred way of doing it. – tadman Sep 17 '13 at 01:20