Standard datetime stamp field filter by range issue

[kdiamond]

I have been using this table for 4 months and the datetime field range filter has worked fine. This morning I found that the date range picker is acting very odd. When you pop open the calendar and pick a date, the field shows what looks like a format string, for example, if I click on October 27th, it shows %Y-%10-%27 in the field. If I just replace it and hand type the date I need, it throws an unrecoverable error and I must unpublish the field, reload the list, then reactivate the field just to get past it.

Error: 0 - DateTime::__construct(): Failed to parse time string (27b 00:00:00) at position 0 (2): Unexpected character

(I am sorry I don't know what could have changed this and I know it worked fine last week).

Desperate - thanks for any help!

 

[troester]

Are you sure exactly this element was working?

The format is looking like the old J!2.5/Fabrik3.0 format (%Y-%m-%d) which was changed in Fabrik3.1+ to Y-m-d etc).
Check your format settings.

 

[kdiamond]

I did not change the format settings, and my DEV system has the EXACT same element settings and as of Friday is not having this issue.

 

[troester]

So what is your list and form format setting in this element?

 

[kdiamond]

the default - untouched. As this is the standard fabrik field created for new lists, I have not made ANY changes to it's setup, other than to enable a range filter on it, which has worked fine for 4+ months.

 

[kdiamond]

AS FYI - I zipped the plugins > date element folder from the working DEV system and unpacked it in my production system and it made no difference.

Please check it out if you need to to save time - the site is in my sites list.

 

[troester]

Which list (frontend)?
Which element (backend)?
There's no backend access, it's protected by .htaccess

 

[kdiamond]

Is there a way we can IM? Skype perhaps? yes, backend is protected. Front-end- log in and under the admin menu you will see 'Admin Event Reports'. Pop open the filter and select a date then you will see the issue. If you can IM or otherwise I can get you to the backend settings.

 

[troester]

It's something with your Joomla template.

With the Protostar template (append ?template=protostar to your URL) the filter is working fine.

 

[kdiamond]

Wow- yes - you are right. There is a gantry setting called 'Client-Side Date' that I turned on so that my home page would show the correct time (CDT, not UTC) and that must have caused it. I never would have put that together. THANK YOU.

On that note - there are two areas where date queries are causing a problem that led me to this.

1. On the main header bar the time shows UTC not Chicago time. Joomla is set to Chicago time as is mysql
2. When I do list filters that use dates, the filters are based on UTC. For example - If it's 8pm on October 2nd, the list shows records for October 3rd (its the 3rd in UTC) even though the query says bring back only today and earlier records.

Thank you again!!!

 

[rob]

Hi

for 1, is that something in Fabrik - or elsewhere, I'm not sure what you are referring to when you say "main header bar" ?
for 2. Could you give me a screen shot of your element's date filter settings? Is this a prefilter or the actual element filter?

 

[kdiamond]

Howdy-

1. You can see it top right corner of the home page. www.pourtasticllc.com
2. It's a database join WHERE clause note the and refers to now():

where {thistable}.Job_Refrence not in (SELECT event_name FROM pet_event_reports)
and {thistable}.Job_Start_Date <= now()
Order by {thistable}.Job_Start_Date ASC

 

[cheesegrits]

I don't think the main header issue is anything to do with Fabrik.

For 2. that's behaving as I would expect, if your date element is using the default "Store as UTC". Which means we convert dates to UTC when we save them to the database, and apply your TZ when we read and render them. This makes migrating, importing and exporting dates much easier, you just know it's UTC and don't need to worry about TZ's. However, it does mean when handling date comparisons yourself, you'll need to apply your TZ offset by hand, like ...

and CONVERT_TZ({thistable}.Job_Start_Date, 'UTC', 'US/Pacfic') <= now()

... or whatever your TZ code is ...

https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

-- hugh

 

[rob]

lol I was going to add that info to the database join wiki page, but its already there.
http://fabrikar.com/forums/index.php?wiki/database-join-element/