Tip: when creating a calendar field, make sure to set Data Type to DATETIME

[René Kreijveld]

I have been testing a lot with a birthday field. Dates are stored in UTC time in the database.
This can cause problems of you have your website running in a different timezone (Global configuration > Server tab > Location Settings > Website Time Zone.

To have the conversion between the local timezone and UTC working correctly you must set the Data Type (Component Builder > Fields > Edit field > Data Base tab > Data Type).

Initially I set the Data Type to DATE. But then you will get conversion problems.

Hope this helps anyone. I've been messing around with it for a few days. Many thanks to Tony Partridge for also looking into this and pointing me in the right direction.

[TLWebdesignNL]

Well it depends on what date you are storing. If you are storing time of course you need DATETIME. if you only store d-m-Y, DATE should be sufficient since you don't have any time to worry about.

Op donderdag 28 februari 2019 12:13:55 UTC+1 schreef René Kreijveld:

[René Kreijveld]

Op donderdag 28 februari 2019 13:27:00 UTC+1 schreef TLWebdesignNL:

Well it depends on what date you are storing. If you are storing time of course you need DATETIME. if you only store d-m-Y, DATE should be sufficient since you don't have any time to worry about.

Unfortunately not Tom...

If you set the Data Type to DATE there are some serious problems. Here are steps to reproduce.

Set your Joomla website to Website Time Zone: Amsterdam in the Global Configuration.

Create a calendar field for a birthday.

Set the format to %d-%m-%Y.

Set filter to user_utc.

Set showtime to false.

Go to the Data Base tab. Set Data Type to DATE.

Set Data Default to Other.

Set Other Default to 0000-00-00.

Add the field to an Admin View in a Component and compile.

Add a record. Use the caledar date picket to shoose a date, for example 19-01-1966.
When I click Save, the data is saved and the record is re-displayed. the date then shows 18-01-1966.

This does not happen when you set the Data Type to DATETIME.

[TLWebdesignNL]

Wow didn't notice that. Seems like a joomla bug not? When i set it to datetime it saves the date with 23:00:00 in the DB. i guess that's where the problem comes from. If it's only DATE. Joomla uses 00:00:00 probably and then converting that to UTC makes it 23:00:00 the day before.

Op donderdag 28 februari 2019 13:34:35 UTC+1 schreef René Kreijveld:

[René Kreijveld]

Op donderdag 28 februari 2019 14:13:32 UTC+1 schreef TLWebdesignNL:

Wow didn't notice that. Seems like a joomla bug not? When i set it to datetime it saves the date with 23:00:00 in the DB. i guess that's where the problem comes from. If it's only DATE. Joomla uses 00:00:00 probably and then converting that to UTC makes it 23:00:00 the day before. 

I guess it is not a Joomla bug. Joomla needs the time to convert a date + time to UTC.

A DATE field doesn't store the time, a DATETIME field does.

What I think is missing in the documentation is, that if you want a calendar field (even if you don't need to display or pick the time), you allways need store it as a DATETIME field in the database :-)

[René Kreijveld]

What I think is missing in the documentation is, that if you want a calendar field (even if you don't need to display or pick the time), you allways need store it as a DATETIME field in the database :-)

And by that I mean in the Joomla documentation: https://docs.joomla.org/Special:MyLanguage/Calendar_form_field_type 

[TLWebdesignNL]

Ah yeah i figured. Just briefly looked into how i could submit changes but it's not easy to do it looks like. Too much hassle for sure.

Op donderdag 28 februari 2019 14:18:03 UTC+1 schreef René Kreijveld:

[TLWebdesignNL]

Jut recently i had another problem with this. Changing my site configuration from UTC setting to Amsterdam. It saved my dates with 00:00:00 as a day earlier at 23:00:00. Like you said before joomla stores these dates as UTC. In order to display them correctly again you have to use JDate to display the date correctly. adding the offset to the date. But only is this needed if you have in your global configuration server timezone set to anything other than UTC. because changing this (and having your date field property set to server UTC) it will calculate the UTC based off this value.

$config = JFactory::getConfig();
$offset = $config->get('offset');
echo $offset;
echo "<br>";
$date = new JDate($this->item->geboortedatum, $offset);
echo  $date->calendar('l d F Y - H:i:s',true,true);

The above code i used to check what happens and the calendar function is used to display dates with names in it in my locale. Before i used strtotime and strfrtime php functions to do this. But i guess this is a more "joomla" way of doing it. and it takes into account the offset. This should fix our date problems for people not having the correct date because of the conversion back to UTC before the date gets saved in the DB.

When i started my project we had this discussion and i changed my date fields to datetime fields. I now converted them back to fix the time conversion issue and somehow it is still showing the correct dates. So it makes me wonder what was going wrong before. I don't think you need to have DATETIME fields if you don't need time in it. Seems to be working for me now.

Op donderdag 28 februari 2019 14:32:26 UTC+1 schreef TLWebdesignNL:

[TLWebdesignNL]

Ok i take it back that the date doesnt render back day before. It still does. Hmm

Op vrijdag 20 september 2019 12:27:13 UTC+2 schreef TLWebdesignNL:

[TLWebdesignNL]

OK Some more testing. it only does this when i have timezone set different from UTC so i even calculates back a date without time. This is not the way it should function if no time is selected. Joomla should do take into account if the field has a "time" value in it. A possible fix is setting the filter type to "none" in a calendar field with time disabled and DB type "DATE". But this fix only works is your formatting is the same as mysql 0000-00-00. Dutch formatting makes it so no value is saved because we told joomla to save the date like it was entered.

Llwellyn do you know if this is due to how JCB saves the data in the database? Or is this a Joomla issue?

Wondering what the best way to fix this would be. Of course Joomla should check if time is disabled in calendar field and then ignore timezone settings and do the rest of the formatting accordingly. But that is not how joomla works at the moment unfortunately.

Kind regards,

Tom

Op vrijdag 20 september 2019 12:35:17 UTC+2 schreef TLWebdesignNL:

[TLWebdesignNL]

Wow i'm talking a lot to myself here haha. I did some digging into Joomla and found the problem and the fix. Opened an issue on github: https://github.com/joomla/joomla-cms/issues/26365 Hope the Joomla Team will like this fix.

Op donderdag 28 februari 2019 12:13:55 UTC+1 schreef René Kreijveld:

I have been testing a lot with a birthday field. Dates are stored in UTC time in the database.

[TLWebdesignNL]

OK René,

Now for real. i found the right solution to the problem. After posting to the github someone came up with the "translateformat" value for a calendar field. And this is exactly what we want. Because it uses a language string to format it in NL. Based on showtime it either used time display or not. So if we want to use DATE for the calendar mysql field we should set translateformat to true. and filter can be either server_utc or user_utc and it won't show day before like it does with "format" attribute.

Example XML:

        <field
            type="calendar"
            name="geboortedatum"
            label="COM_COMPONENT_TALENT_GEBOORTEDATUM_LABEL"
            default="01-01-1980"
            description="COM_COMPONENT_GEBOORTEDATUM_DESCRIPTION"
            translateformat="true"
            filter="server_utc"
            required="true"
            todaybutton="true"
            minyear="-99"
            maxyear="-18"
        />

We should also consider improving the default calendar fields in JCB. For instance the modified or created dates. Llewellyn should i open a github issue for that?

Op vrijdag 20 september 2019 13:53:47 UTC+2 schreef TLWebdesignNL: