Hi,

Finally I have reason to use K2 in a project. It's been many years that I've looked at it, and it's impressive how far and well it has grown.
Just for the record, I'm using K2 2.10.4 from Github with J! 3.9.28.

However, there's one huge issue with K2.
TL;DR: Why does K2 not store all*** dates/times in UTC in the database? That's the convention everywhere, all extensions (at least the ones I know) are doing it, and that's what makes sense and makes Joomla working properly with system global timezone versus user timezones (TZ).
Instead, K2 stores many or most dates/times, at least the ones visible = relevant for users, in the TZ as set in the user's account. Which makes no sense... none at all.

So, here's the long story:

I've seen many posts here, usually triggered by users wondering why a new or edited item ends up "pending". These are the users who are -- rightfully so -- having in their user accounts their timezone (TZ) set ahead of the system's global TZ. The "solution" they're being offered is for them to their individual user TZ to the same as Joomla's global one, means "default". This is not a solution, but results in defeating one of Joomla's great features: every user being able to set and see dates/times in their respective TZ as set in their account. The way K2 is handling it, that's just not possible.

K2 users, who have all user accounts set to the default system TZ or "behind/before" GMT (= GMT minus something), will probably never notice, indeed. But here am I (and obviously I'm not alone): I'll have users from all corners of the world logging in, and in both frontend and backend they should set and see dates/times in their respective TZ as per their Joomla user account, while guests in the frontend should see J!'s system time. Again, works everywhere, except for K2. And only because K2 stores critical dates/times in whatever the user's TZ is... instead of UTC.

Example:
J! global system TZ = UTC (so guests in the frontend see UTC/GMT).
My user TZ = GMT+8 (because that's where I happen to be).

I create a new K2 item. For "Creation date" and "Start publishing" I'm seeing my current date/time = say 2021-08-11 21:00:00 (while UTC and expected storage is 2021-08-11 13:00:00).
Unfortunately, and wrongly so, on save this exact "string" I am seeing is stored in the database. Not UTC.
As a result, what happens?

• In the backend item listing:
  • "Created" is 8hrs later: 2021-08-12 05:00:00.
  • "Published" is promptly "pending".
• Frontend:
  • First of all: nothing. Because it's "pending". For 8 more hours. Grrr.
  • If it would be visible (or after at least 8hrs later)...
    • ... a guest user sees "Created" still and wrongly in my TZ = as 2021-08-11 21:00:00 (instead of UTC = J! system TZ = 2021-08-11 13:00:00).
    • ... a logged-in user with his TZ set to e.g. GMT-5 sees 2021-08-11 16:00:00 (instead of 2021-08-11 08:00:00).
• Now I need to edit the item. Oh. My. God. Because this is what happens -- on each and every save in edit mode:
  • The "Creation date" and "Start publishing" times are pulled from the database, and 8 hours are being added! As if they are UTC in the database... but they aren't! If I hit "Save" 10 times, I get 80 hours added on both those times!!!!!!!!
  • Means also that, after a few edits (or just clicking "Save"), the item is pending = vanished from the frontend for X hours.

Nicely (I'm tempted to say "strangely", lol), "modified" dates/times are stored in UTC, no matter what. At least for K2 items.

From other posts here, I sense that the same disaster happens for K2 comments and who knows what else as well...

So, do I have to hack "my" K2 to get it working as it should? Or can "we" = all K2 users get it so it stores all dates/times in UTC as per convention?

Thanks in advance for help from the K2 devs!


*** Actually, K2 stores some few dates/times correctly in UTC, e.g. the "modified" date/time of items. Why only those few? Makes even less sense, causes even more confusion.


EDIT:
Added to "Issues" in Github: github.com/getk2/k2/issues/540

(Deleted because I had used "code" tags, silly me. So, please see following post.)

Aaaaah... shouldn't have used the code tag here above. Can't even delete that post. Sorry.
Here we go again:


Found the cause.

For example, for item creation/edition, in /administrator/components/com_k2/models/item.php this is the current code around lines 118-128:

        $config = JFactory::getConfig();
        $tzoffset = K2_JVERSION == '30' ? $config->get('offset') : $config->getValue('config.offset');
        $date = JFactory::getDate($row->created, $tzoffset);
        $row->created = K2_JVERSION == '15' ? $date->toMySQL() : $date->toSql();

        if (strlen(trim($row->publish_up)) <= 10) {
            $row->publish_up .= ' 00:00:00';
        }

        $date = JFactory::getDate($row->publish_up, $tzoffset);
        $row->publish_up = (K2_JVERSION == '15') ? $date->toMySQL() : $date->toSql();

Means that here currently both "created" and "publish_up" dates are offset always and only by the J! global system timezone, no matter what, and stored accordingly.
Instead they should be offset by the user's timezone, and only if it's a guest or if the user's timezone is default, then by the global system timezone.

Dear developers, please fix this here and in all other occurrences where you are using a similar wrong offset, like seemingly also comments and other areas. Sure: the sooner, the better.

Thanks!

Hi,
And thanks for your reply.

However...

My guess is that your MySQL/MariaDB instance is already using some time offset and this may be causing issues.

Not sure why you would say this, because (1) a server which would alter the time stored in the DB by an application should be taken offline asap, (2) Joomla and its components are to store times in the DB in UTC, no matter what timezones (TZ) are set as J!'s global or for individual user accounts. That's the convention, it makes really good sense for everyone and everything, and except for K2, all J! components I know of are doing this properly, most commonly in the typical datetime format (Y-m-d H:i:s), some few in Unix Epoch format, but always in UTC - after proper offset. Again, not K2.

Never had an issue with this. Honestly.

Not if the TZ of your user account is set to "default" (= J! global system TZ) or before it (e.g. if J! global TZ is UTC and the user TZ is "GMT -x"). At least then you don't notice that you have an issue.
But, as stated, any user who has his account set ahead of J!'s global TZ (= plus some hours) has a huge problem. And, as also stated, you have several of those posts here in the forum, where K2 users complained that their newly created items are always ending up "pending", and it gets only worse with every save of an item. I could dig up and post many links to such posts here in the K2 community, and elsewhere across the web there are even many more where folks are scratching their heads about this irrational behaviour of K2. Well, that's only the most obvious symptom... the culprit is the code.

In any case, if we're to use a timezone offset, then that would be the site's, not the user's timezone offset. A site may be managed by users in different timezones, however K2 content should be published using a common timezone.

Unfortunately, that's the common "solution" presented to K2 users, except it is not one. K2 is defeating J!'s ability to define a global system (= site) TZ which guests and "default" users use and see, plus allowing user accounts to set their own local TZ, so that when they're logged in, they always see all times in this = their own local TZ... all while anything gets stored in the DB in UTC.

Impossible with K2, at least without a hack of core files as it stands now (and since ever, I guess). K2 standing in its own way of avoiding an obvious problem and being really useful in the J! ecosystem - for every K2 user.

With a global Internet, event or meeting times, or generally a good user experience, or simply when only managing K2 items no matter what TZ is set in J! globally or for the individual user... it shouldn't be an issue, J! has it on board, and it has absolutely nothing to do with server and its own TZ.
All it takes is the component coder to do a quick check if a user has a TZ set in his params. If so, offset this against UTC. Else offset J! system TZ against UTC. And always only store UTC in the DB.

Not a biggie, just very few more lines in K2... it's now a while ago, but last time I checked I think it was in only two or three files. Wouldn't even cause issues for current K2 users, since they're all staying in "default" TZ or "further west", typically forced (and told so!) to dismiss a great native J! feature.
Haven't decided yet, but should I end up using K2, I'll do it myself... actually I'd have to do it, otherwise K2 would be literally useless - sorry. How much greater if K2 devs would get it, respect the convention, utilise the native J! functions, prevent some users needing to hack the K2 core, and make all K2 users happy (if they know it or not, haha)...

If OTOH you'll say, "no, we don't want to do this, we want to continue to store whatever TZ we like in the DB, and we prefer forcing K2 users to not be able to use the native J! feature of picking and using their own local TZ", then I'd be very much interested what your reasoning behind this would be. And I'm sure I'm again not alone.

Thanks!