Symphony.

Hello,

when I filter datasource by $today, the SQL seems to be filtering dates between, for example 2013-01-17 22:00:00 and 2013-01-18 21:59:59 and that's not correct. It should filter between 2013-01-18 00:00:00 and 2013-01-18 23:59:59.

Why is that and how to fix it?

Sounds like a bug. What's your PHP version?
Does it work correctly when you filter by today (relative date, no parameter)?

PHP5.4.3 (WAMP, Windows 7), but it seems to do it on production server too...

I saw on field.date.php-file some calls to DateTimeObj::getGMT()...

Here: https://github.com/symphonycms/symphony-2/blob/master/symphony/lib/toolkit/fields/field.date.php#L271

Is it because of that?

Edit: That line is an example, there are other calls to that method too.

Hm, or not of those.

What you mean with by today? I have a datasource and I have selected date-field and for filter value I have put {$today}. If I put just plain text today, isn't it literal value today?

No, the relative string will be converted to a DateTime object.

What you mean with by today?

The date field should be able to filter by relative dates. For instance, today or + 3 days.

Maybe you messed up timezone settings, either in your config or in your database?

Nils, ok. When I type today into filters value field, I get same day, but in both, the time is 22:00:00.

Michael, I have right timezone, both in the php.ini and in Symphonys config.php-file. Also, if I do SELECT NOW() on MySQL console, I get the same time as my local machine has... Is there's some timezone setting in MySQL?

Is there some per-user timezone setting in Symphony?

Yep, you can do SELECT @@global.time_zone, @@session.time_zone; which will probably return SYSTEM for both values. Anyway I don't think that Symphony relies on this MySQL setting.

Which version of Symphony is it? Which is your timezone? Can you post a row from the date field's table? (The table structure differs for Symphony versions 2.2 and 2.3, but in both cases there should be some timezone information in the values.)

Symphony version is 2.3.1. Timezone is Europe/Helsinki (+2 hours)

And if this is the right table, this is a row I created today for test: 11 53 2013-01-19T14:58:15+02:00 2013-01-19 12:58:15

Does it seem right table?

Still, I think there could be a bug somewhere since the start of the range is yesterday. It shouldn't, it should be same day, 00:00.

Edit: Typo. And it goes yesterday when I use {$today} as filter value.

Does it really even matter, since if it always compares GMT to GMT instead of my TZ time to my TZ time?

Hmmm, that looks right to me.

So I have no idea what's wrong.

Do you have a plain vanilla install to test if it happens there as well?

I can test.

But, I took those "wrong" date values from SQL queries in profiling page. So, maybe it is by design that date field stores GMT version of the value and converts the filter value to GMT and then compares GMT to GMT.

@brendo will know better.

Can you simply disregard the SQL queries and run your filtering on some real entries (using test dates)?

Perhaps the results of this could help clarify things: Symphony Tests.

install the Symphony Tests extension and run ?tests=symphony.datetimeobj on the Frontend (just as if you were debugging).

Michael, I tried fresh install and same thing (as an answer to your question about vanilla Symphony install).

Lewis, I tried but got this:

Symphony Fatal Error: Argument '$value' is not of type 'string', given 'NULL'.
An error occurred in \some-path-to\symphony\lib\toolkit\class.general.php around line 1322

Have you tried filtering on real entries, like I proposed above?