GridView filter for Date column

Hi, I’m new to the board so sorry if this is in the wrong section/missing something/etc.

I have a GridView of some data with a couple dates, such as "Date Created". Right now these fields are stored in MySQL as a DATETIME or VARCHAR(20) (the code is a bit messy at the moment).

We’re planning on a multinational user base, so we can’t use the MySQL server’s local timezone. Ideally, these dates would simply be type INT and contain the unix timestamp. Everything about this works great, except the GridView filters. Formatting the dates to be readable in the GridView is easy, but the filter compares what you type in to the stored value, not to the formatted value. So if I type in “2010”, it’s going to look for all the data with unix timestamps equal to “2010”, not dates that start with “2010” as it does with string-based dates.

The only thing close to a solution I’ve found so far is to modify the model’s search() method, specifically these things:

$criteria->compare('createDate',$this->createDate,true);

The problem is, you can only format the filter value ($this->createDate), not the actual DB column.

We really want to be able to use these filters, and we really don’t want to use MySQL’s DATETIME or TIMESTAMP formats. Anyone have any ideas? Thanks so much for your help.

in model->search() $this->createData is the value entered in the filters… so you can format is anyway you want to get the result from the database…

Yeah, that’s what I was going to try. But the user is entering in data that’s already ‘formatted’, since they’re entering a date, not the timestamp. Potentially, I could run strtotime() on the string they enter to get a timestamp, and compare that to the database column. But the problem is, then they have to type a complete, perfectly formatted date, and most likely it will only find exact matches. Whereas if it were comparing with text, they could type in, say, “201” and it would find everything with years 2010, 2011, 2012…

This is not simple… there should be some rules for the filter…

In your example you talk about the year… but how would you search the database if they would enter just the month… or the day…