?
avatar TLWebdesign
TLWebdesign
20 Sep 2019

Steps to reproduce the issue

Create a calendar field with format: format="%d-%m-%Y" and filter="server_utc" and mysql database with DATE field.

Have server_utc set for example Amsterdam/Europe

Edit calendar field and save.

Expected result

Joomla to save the date in mysql format without taking timezone into consideration since i have no time. Only a date. Since showtime is false.

Actual result

Joomla still converts my date with the time $offset and thus changing my date to the day before the specified date.

System information (as much as possible)

Joomla 3.9.11

Additional comments

I did some digging and also found the solution to this problem. In older issues they stated to set filter to "none" or i guess "RAW" would do the same. This would work if you have english notation like 0000-00-00. But any other notation won't save to a DATA Mysql field. Changing the Mysql Field to a generic varchar would fix but would severely limit the possibilities in queries and such.

My fix: check if showtime is false and then don't bother adding the offset inside the filter. Seems like it should behave this way by default. Since i don't know how to do a commit properly i just open this issue and let you know what i think should be changed:

in File: /libraries/src/Form/Form.php on line 1339 & 1384 change this

$return = \JFactory::getDate($value, $offset)->toSql();

to this:

$return = ($showTime) ? \JFactory::getDate($value, $offset)->toSql() : \JFactory::getDate($value)->toSql();

This should fix the problem for all us users having a different notation than 0000-00-00 and
still be able to set proper mysql database type and calendar field filter.

another possibility would be to add a filter that would only format the calendar field. But that seems unnecessarily complicated to me.

Kind regards,

Tom

avatar TLWebdesign TLWebdesign - open - 20 Sep 2019
avatar joomla-cms-bot joomla-cms-bot - change - 20 Sep 2019
Labels Added: ?
avatar joomla-cms-bot joomla-cms-bot - labeled - 20 Sep 2019
avatar TLWebdesign TLWebdesign - change - 20 Sep 2019
The description was changed
avatar TLWebdesign TLWebdesign - edited - 20 Sep 2019
avatar brianteeman
brianteeman - comment - 20 Sep 2019

Changing the Mysql Field to a generic varchar would fix

And what happens when you change it to a DATETIME field - which is what joomla uses everywhere iirc

avatar Bakual
Bakual - comment - 20 Sep 2019

Actually setting the filter properly is the solution to this. That filter controls how timezones are applied. And as you wrote it works for the english notation. Hacking the form filter is a wrong approach.

If you use a different date format (like the german one you posted), then PHP can't properly determine the date anyway. However if you use the translateformat feature from the calendar, then you don't have to bother about the date format. Joomla will automatically transform it back to the english format prior to saving and it should work also with timezone shifting off.

Please try that and see if your issue is solved.

avatar TLWebdesign
TLWebdesign - comment - 23 Sep 2019

Changing the Mysql Field to a generic varchar would fix

And what happens when you change it to a DATETIME field - which is what joomla uses everywhere iirc

That was our fix before. But that is not really a fix But what Bakual said is working it seems like. Strange though that this information is nowhere to be found.

avatar TLWebdesign TLWebdesign - change - 23 Sep 2019
Status New Closed
Closed_Date 0000-00-00 00:00:00 2019-09-23 11:13:36
Closed_By TLWebdesign
avatar TLWebdesign
TLWebdesign - comment - 23 Sep 2019

Actually setting the filter properly is the solution to this. That filter controls how timezones are applied. And as you wrote it works for the english notation. Hacking the form filter is a wrong approach.

If you use a different date format (like the german one you posted), then PHP can't properly determine the date anyway. However if you use the translateformat feature from the calendar, then you don't have to bother about the date format. Joomla will automatically transform it back to the english format prior to saving and it should work also with timezone shifting off.

Please try that and see if your issue is solved.

Yes it did! Thanks for pointing me in the right direction. I couldn't find anything about this online at all. And the documentation doesn't really explain this very well either. Thanks a lot! Of course then what i proposed is indeed not the right solution!

Kind regards,
Tom

avatar TLWebdesign TLWebdesign - close - 23 Sep 2019
avatar Bakual
Bakual - comment - 23 Sep 2019

Strange though that this information is nowhere to be found.

If something is missing on the documentation (https://docs.joomla.org/Calendar_form_field_type), feel free to improve it. It's a Wiki.

Add a Comment

Login with GitHub to post a comment