serious date filter issues

[trob]

latest GitHub (2012-03-06 03:07:35), Joomla 2.5.1
all date elements have list and form format %d-%m-%Y, UTC, no time selector

!!Markdown is displaying 3 underscores as bold, so I changed to _ x _

1. date element, list filter=field
   selecting 16-02-2012 from date selector --> SQL = WHERE ( test.date_time BETWEEN '16-02-2012' AND '2012-02-16 23:59:59' )
2. same with list filter= dropdown
3. same with URL-filtering:
   listid=1&test_x_date_time[value][]=18-02-2012&resetfilters=1&fabrikdebug=1
   --> WHERE ( test.date_time BETWEEN '18-02-2012' AND '2012-02-18 23:59:59' )
4. URL filter with BETWEEN ignores second date and condition
   index.php?option=com_fabrik&view=list&listid=1&resetfilters=1&test_x_date_time[value][]=20-02-2012&test_x_date_time[value][]=22-02-2012&test_x_date_time[condition]=BETWEEN&fabrikdebug=1
   --->WHERE ( test.date_time BETWEEN '20-02-2012' AND '2012-02-20 23:59:59' )
5. different placeholder handling in forms with/without recording to database:
6. form without database
   jump page = index.php?option=com_fabrik&view=list&listid=1&test_x_date_time[value][]={_x_date_time}&resetfilters=1&fabrikdebug=1
   is redirecting to
   listid=1&test_x_date_time[value][]=2012-03-27 23:31:06&resetfilters=1&fabrikdebug=1

• form with recording in database
  index.php?option=com_fabrik&view=list&listid=1&test_x_date_time[value][]={datummit_x_date_time}&resetfilters=1&fabrikdebug=1
  is redirecting to
  listid=1&test_x_date_time[value][]=01-03-2012&resetfilters=1&fabrikdebug=1

---

Date element range filter is ok:
selecting 18-02-2012 and 21-02-2012 via date selector
--> SQL = WHERE ( test.date_time BETWEEN '2012-02-18 00:00:00' AND '2012-02-21 23:59:59' )

[xtrgeo]

I can confirm 4, adding as a comment that it only takes in mind the first date a trob said AND it filters like this:

"Return all the the records that has their date element SMALLER or EQUAL THAN the first day of the url"
(Strange as the date in url has the "=")

Can also confirm 6 and 7

[xtrgeo]

just updated from github and noticed that it now works in a different way!Now the filtering is made like:

"Return all the the records that has their date element SMALLER or EQUAL THAN the SECOND date of the url" !!!

[cheesegrits]

We're working on the date filtering. I think some things got fixed in commits over the last 24 hours or so, I'm continuing to test the issues raised here.

[trob]

Uups, now date_time is always in SQLformat (list view, form/detail view) and date selector has vanished
GitHub 2012-03-10 00:07:30

[pollen8]

ive changed things so that url filters now have to be in mySQL format. This makes things a lot more consistent with the fact we submit dates in a mySQL format.

I tested all filters and things seem to work ok for me,

Which browser were you using for the vanished date selector and where did it vanish from (list filter view or the form view)?
For me in firefox I see it

[xtrgeo]

strange.just updated from github and it works exactly as I say in my previous comment here. Also,the date in the url is in list format(form is recording to database).
Using firefox

[trob]

Sorry, ignore my last post;
For testing I had changed the element to field. After setting back to date all is displaying right (I didn't test the filters yet).

The reason is a prefilter issue: using a date element
WHERE my-element<= now(), type=noquotes (which is working in fabrik2)
gives error 500; DateTime::__construct() [datetime.--construct]: Failed to parse time string (now()) at position 3 ((): Unexpected character
after setting the element to field (only in fabrik, not in the DB) the prefilter is working

[trob]

So, if the URL should contain the SQL format we have to use {element_raw} in the jump page URL?

This looks a lot better, but there are still some issues with the time:
all elements (list and search form) have list and form format %d-%m-%Y, no time selector

As I don't set a time I think it should be 00:00:00 but any date is stored in the DB with the actual time (which you can see shortly during save when it's displaying the date in SQL format)
and the actual time is also included in the {element_raw}

So, if I've inserted a row this afternoon with a date 07-03-2012 it's stored in DB as 2012-03-07 16:23:33

Now (21:30) I select 07-03-2012 in the search form,

the generated URL is ...2012-03-07 21:30:24
the SQL is date_time` BETWEEN '2012-03-07 21:30:24' AND '2012-03-08 20:30:23' (!! offset is only 22:59:59, I assume it should be 23:59:59)

and the row is not found.

So to handle only dates without time I have to set all date_time elements (in list and searchform) to
default (eval): return date('Y-m-d');
store as local time

Some more remarks:
even if "Store data as UTC" is set {element_raw} inserts local time into URL (but UTC time is stored)

if "Time selector"=yes {element_raw} inserts e.g. 2012-03-10 00:00:00__,00:00:00__ , appending a additional time string (and breaking all date filtering with error 500: DateTime::__construct() [datetime.--construct]: Failed to parse time string (2012-03-10 00:00:00,00:00) at position 20 (0): Double time specification)

[pollen8]

hi
the time issue should now be resolved.
And for me now the search form works ok

I couldn't replicate the last issue where if "Time selector"=yes {element_raw} inserts e.g. 2012-03-10 00:00:00,00:00:00. Can you check that my other changes havent fixed that and if they haven't can you provide me with more info on testing that one?

thx
Rob

[trob]

Form not recording to DB, date element list+form format = %d-%m-%Y, time selector=yes

Cache cleared

still this issue: with time selector {element_raw} inserts e.g.
2012-03-10 00:00:00,00:00
(or 2012-03-10 03:15:00,03:15 - if time 03:15 selected)
in redirect URL
index.php?option=com_fabrik&view=list&listid=1&test___date_time[value][]={___date_time_raw}&resetfilters=1&fabrikdebug=1

without time selector it's ok

[xtrgeo]

I can confirm that using a form that RECORDS to database(not a good thing cause every search is recorded to the database) works ok now, with time selector = o

When I tried to test it without recording to database i got this error:

DateTime::__construct() [datetime.--construct]: Failed to parse time string (ΣΤΕΦΑΝΙΔΗΣ) at position 0 (�): Unexpected character

I tried to search the table first with the common search and i got the above error...

[trob]

It's working on my site with
*dates without time selector
*jump URL in search form (not recording to DB) composed with {___date-element_raw}

Tested with a search form with only one date element (should be the same as enabeling list settings/filter for this element)
and with a search form with 2 date elements + condition BETWEEN

You must clear your browser cache after updating.

[pollen8]

I'm kind of confused now where we are with all this. Is there an outstanding issue here still or not? If there is an issue can a video showing what you are doing be added please?

[pollen8]

I'm closing this ticket - if you have additional date filter issues please open a new ticket