Date range filter issues

[Raindog]

Link to test site: http://www.gorod-dudinka.ru/fabriktest/

Clean Joomla 2.5.6., Fabrik updated to last github 3.0.5-916. Debug is on.

Simple list with date element. Date element settings in attachment. Both List and Form format for element is %Y-%m-%d, so dates always saved with time 00:00.

1. If we select in filter with date picker range 2012-07-24 ? 2012-07-24 and press "Go" we get "No record", what is wrong. I think it happens because date picker sends to filter current time (it is visible for moment). But if we now press "Go" for second time, we get correct result.

2. If we select by hand or with date picker only first field in range with 2012-07-24 and leave second field blank, we get all records, what is wrong. But if we now press "Go" for second time we get correct result.

3. If we select by hand or with date picker only first field in range with 2012-07-15 and leave second field blank, we get records from range 2012-07-01 ? 2012-07-15. If we now press "Go" for second time we get range 2012-07-15 ? 2012-07-24.

4. As I can see, with one blank field in date range, intention is to fill empty field with current date. But I think, for users it can be confusing, especially if we use toggleable filter. I suggest always fill blank field in date range with the same date, what was selected in another, not empty field. So, if we select first field in date range with 2012-07-15 and leave second field empty, second field must be filled with same date - 2012-07-15.

5. And one more, is it possible to hide changing of date format then we press "Go" in filters or "Save" in forms? For example, if I select format for date elements %d.%m.%Y, then I save record, I see all date elements changes for short time to %Y-%m-%d %H:%M.

 

[Raindog]

*friendly bump*

 

[cheesegrits]

The brief showing of the time in the visible field when hitting Go is something we intend to improve at some point, once we have the basic functionality of the date filtering solid.

I'm looking at your other issues now.

-- hugh

 

[cheesegrits]

OK, I see what's going on with the initial submit of a ranged date filter, and why it works subsequently, testing some fixes now.

-- hugh

 

[cheesegrits]

OK, I just committed what i think should fix that "first submit applies time" thing.

Can you update and test that?

Once we've resolved that issue, I'll look at the "blank field" issues.

-- hugh

 

[Raindog]

Made update. No changes - first submit (or after clear filter) with date picker applies time.

 

[cheesegrits]

Hmmm, I'm unable to replicate this after my changes, for a date which does not use a time format (i.e. no time part in the date format specifications, and no time widget being used).

Here's a screen cast of my test case.

http://screencast.com/t/8wczrUzfexod

I showed the wrong query in the debug to start with, it's the second one, where you can see the "BETWEEN" clause, which covers the 24 hours period making up the 26th, expressed with time zone correctly applied (I'm on CST, which is GMT-6) to offset from midnight on the chosen date to one second before midnight on the following date.

Can you confirm that the date element you are filtering on is set up as per my example?

-- hugh

 

[cheesegrits]

PS, I did do a "Clear Filters" immediately prior to capturing the screencast, btw.

if you date is set up like mine, can you check your copy of ./plugins/fabrik_element/date/date.php, around line 1516 should be this code:

				// $$$ due to some changes in how we handle ranges, the following was no longer getting
				// applied in getFilterValue, needed because on first submit of a filter an arbitrary time
				// is being set (i.e. time "now").
				$value[0] = $this->setMySQLTimeToZero($value[0]);
				$value[1] = $this->setMySQLTimeToZero($value[1]);

-- hugh

 

[Raindog]

Sorry, Hugh, my mistake! date.php wasn't updated. Now I can confirm - "first submit applies time" fixed.

 

[cheesegrits]

OK, I'll have a hack at the "only one date filled in" thing.

-- hugh

 

[willisbryan]

Hey guys this is the same problem that I was having and wrote about a few weeks ago. I updated the build and its filtering but its a day off. For example:

a. I filter results by 7-30-2010 - 7-31-2012 I get all the results for 7-30-2012 and one or zero results for the 31st. I live in Arizona so my time zone never changes, I have my local time zone set to Phoenix and am running local time as a setting for my date element.

I entered 17 orders into my website today. When I filter for today it shows me 1 result. If I filter for tomorrow it shows me everything from today. Ive noticed that if I try to change the element search function to a field or autocomplete that I still get the same results.

 

[willisbryan]

I just changed the date filter type to a drop down and noticed that instead of having individual dates in sequential order, for example 7-1-12, 7-2-12 etc. it is listing multiple days separately. For example: I have 7 entries to choose from for 7-28-2012 but no entries to choose for 7-30-2012. So in order for me to see records from 7-30-2012 I would have to choose to see everything from 7-29-2012. Maybe this has something to do with why I am unable to retrieve the proper records for a specific date range. If I show all results with no filters, then group by date, it seems to work fine. Its just when utilizing a filter to sort.

Im not sure if that information helps or not but it cant hurt

Thanks,

Bryan

 

[cheesegrits]

@willisbrian - ah, you are using "store as local"? I did notice when fixing the first issue in this thread why that might be, and even added a note to the code at the point we build the actual two dates to use in the BETWEEN query:

		// $$$ rob 20/07/2012 Date is posted as local time, need to set it back to GMT. Seems needed even if dates are saved without timeselector
		// $$$ hugh - think we may need to take 'store as local' in to account here?
		$localTimeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));

		$date = JFactory::getDate($value[0], $localTimeZone);
		$value[0] = $date->toSql();

		$date = JFactory::getDate($value[1], $localTimeZone);
		// $$$ hugh - why are we setting the 'local' arg on toSql() for end date but not the start date of the range?
		// This ends up with queries like "BETWEEN '2012-01-26 06:00:00' AND '2012-01-26 23:59:59'"
		// with CST (GMT -6), which chops out 6 hours of the day range.
		// Also, see comment above about maybe needing to take "save as local" in to account on this.
		//$value[1] = $date->toSql(true);
		$value[1] = $date->toSql();

I don't currently have a "store as local" example to test with, so if you wouldn't mind, can you test this for me? Find the above code in ./plugins/fabrik_element/date/date.php, around line 1531. modify it to look like this:

		$params = $this->getParams();
		$store_as_local =  $params->get('date_store_as_local', '0') == '1';

		// $$$ rob 20/07/2012 Date is posted as local time, need to set it back to GMT. Seems needed even if dates are saved without timeselector
		// $$$ hugh - think we may need to take 'store as local' in to account here?
		$localTimeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));

		$date = JFactory::getDate($value[0], $localTimeZone);
		$value[0] = $date->toSql($store_as_local);

		$date = JFactory::getDate($value[1], $localTimeZone);
		// $$$ hugh - why are we setting the 'local' arg on toSql() for end date but not the start date of the range?
		// This ends up with queries like "BETWEEN '2012-01-26 06:00:00' AND '2012-01-26 23:59:59'"
		// with CST (GMT -6), which chops out 6 hours of the day range.
		// Also, see comment above about maybe needing to take "save as local" in to account on this.
		//$value[1] = $date->toSql(true);
		$value[1] = $date->toSql($store_as_local);

I *think* that should work, as it'll tell the toSql() method to return the date in local time zone, not GMT, which should then match the values stored for your dates.

Let me know if it works, I'll make the change in github.

-- hugh

 

[cheesegrits]

Re using a date as a dropdown filter, this is problematic, as the actual query to find all the dates will be doing a DISTINCT('yourtable___date'), which will include the time part. We don't start messing with formats, and whether they have time parts, till after the initial select.

At least, I'm assuming that's why you see multiple choices for individual dates. Can you check your underlying table in MySQL, and see if the multiple entries for the days have different times on them?

This is something we've run across before, and would be a major pain in the ass to fix, especially as both Rob and I hate working on the date element, LOL!

But if it's a real problem for you, let me know, I'll see if I can tweak things so only the date part is considered for purposes of DISTINCT() in the filter setup query, if you don't have a time format for that date in list view.

-- hugh

 

[willisbryan]

@willisbrian - ah, you are using "store as local"? I did notice when fixing the first issue in this thread why that might be, and even added a note to the code at the point we build the actual two dates to use in the BETWEEN query:

		// $$$ rob 20/07/2012 Date is posted as local time, need to set it back to GMT. Seems needed even if dates are saved without timeselector
		// $$$ hugh - think we may need to take 'store as local' in to account here?
		$localTimeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));

		$date = JFactory::getDate($value[0], $localTimeZone);
		$value[0] = $date->toSql();

		$date = JFactory::getDate($value[1], $localTimeZone);
		// $$$ hugh - why are we setting the 'local' arg on toSql() for end date but not the start date of the range?
		// This ends up with queries like "BETWEEN '2012-01-26 06:00:00' AND '2012-01-26 23:59:59'"
		// with CST (GMT -6), which chops out 6 hours of the day range.
		// Also, see comment above about maybe needing to take "save as local" in to account on this.
		//$value[1] = $date->toSql(true);
		$value[1] = $date->toSql();

I don't currently have a "store as local" example to test with, so if you wouldn't mind, can you test this for me? Find the above code in ./plugins/fabrik_element/date/date.php, around line 1531. modify it to look like this:

		$params = $this->getParams();
		$store_as_local =  $params->get('date_store_as_local', '0') == '1';

		// $$$ rob 20/07/2012 Date is posted as local time, need to set it back to GMT. Seems needed even if dates are saved without timeselector
		// $$$ hugh - think we may need to take 'store as local' in to account here?
		$localTimeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));

		$date = JFactory::getDate($value[0], $localTimeZone);
		$value[0] = $date->toSql($store_as_local);

		$date = JFactory::getDate($value[1], $localTimeZone);
		// $$$ hugh - why are we setting the 'local' arg on toSql() for end date but not the start date of the range?
		// This ends up with queries like "BETWEEN '2012-01-26 06:00:00' AND '2012-01-26 23:59:59'"
		// with CST (GMT -6), which chops out 6 hours of the day range.
		// Also, see comment above about maybe needing to take "save as local" in to account on this.
		//$value[1] = $date->toSql(true);
		$value[1] = $date->toSql($store_as_local);

I *think* that should work, as it'll tell the toSql() method to return the date in local time zone, not GMT, which should then match the values stored for your dates.

Let me know if it works, I'll make the change in github.

-- hugh

Thank you so very much, the updated code fixed my issue 100%. Please share this with the rest of the community as this may be the answer to a lot of peoples issue. There are a lot of us who have to set to local time because of applications that we are using and I bet like myself, no one has really mentioned it until now.

Thank you again!

 

[cheesegrits]

OK, should be in github now.

-- hugh

 

[Raindog]

*friendly bump*

 

[cheesegrits]

Haven't had time to do this yet, as Rob is away at the Olympics, so I'm pretty much slammed handling support. Bump again when you see Rob rejoin us.

-- hugh

 

[Raindog]

*friendly bump*

 

[rob]

I'm not sure what the bump is for now. It seems the change Hugh suggested is now in the github code, was there another issue?

-Rob