Re: [Tiki-devel] Tracker date-only field timezone problem

Hi Victor (i hate timezones!)

What a coincidence! The nasty timezone thing i uncovered in recurring calendar events is much the same, but different... (see the p.s.)

Are you sure the timestamps stored in the database are in a local "tiki" timezone? I thought they were always converted to UTC for storage and the converted to the user's (or server's) timezone for display, no? Because surely "number of seconds since 1970" has no timezone?

Surely if all the dates were stored in UTC and the conversion from and to the user's displayed timezone was consistent it would "just work" wouldn't it?

And do we use the same "system" for all dates, e.g. trackeritems and calendar events (and create and modified dates etc)? We should be consistent if possible.

I don't much like having two formats of storage, one for dates and another for datetimes, so i think we should change both if we're going to, but that sounds pretty bad and will surely break things.

One cheat i thought of ages ago for dates would be to set the date timestamp as 12:00pm on the day, and then it would only be incorrect in that weird +13h timezone no one lives in... (oh, sorry, New Zealand in their summer) - it was generally agreed that was too hacky :p

Happy to have a session on this and play with some code together some time if that would help - would help me, i always get lost in these things!

jonny


P.S. By the way, the related issue in recurring all-day events is: i enter an all day event for today in my timezone (UTC+1) but something in \CalRecurrence::constructVCalendar then adds one hour to the start and stop times, so my event becomes from 1:00:00 to 0:59:59 the next day, so shows on two days.

P.P.S. I see on my local test 23.x i have my server_timezone pref set to "Europe/Paris" so currently UTC+2 presumably from some tests ages ago, might explain the weirdness above - and if that defines the timestamp being stored in the database, setting that to a timezone that changes for DST twice a year is surely A Bad Bad Idea!? ;)


> On 5 Oct 2021, at 14:39, Victor Emanouilov via TikiWiki-devel <[email protected]> wrote:
> 
> Hi devs and timezone fans!
> 
> Debugging some wiki plugin list range filter problems with tracker date fields resulted in a sad conclusion recently and I wanted to get some input from anyone storing and using dates in Tiki or just your opinions on an upcoming update I am considering.
> 
> Tracker Date fields "Date and Time" and "Date and Time (Date Picker)" called respectively DateTime and JsCalendar fields convert and store their internal data as a timestamp (seconds since UNIX epoch). JsCalendar field uses proper conversion to take into account browser based timezone and also Tiki-defined timezone to store the times as correct point in time depending on the chosen Tiki timezone. Later on, search index stores the timestamps as datetime objects (both mysql and elastic support datetime objects without a timezone identifier attached to it), so timestamps are converted to datetime objects based on UTC GMT+0 timezone. This is the case when we use Date with time field. All is working well here as we always store a specific point in time (a timestamp) and knowing the right time
 zone, we can display correctly. We can also search correctly when we convert incoming dates and times as filters when we know what timezone are they entered in.
> 
> Now, the problem. When we set the tracker date field to be date-only, our UI (JsCalendar and DateTime) still convert this to the Tiki-defined timezone. They do this as our internal representation is timestamp, so we need a specific point in time. Tiki tries to get the selected date at 12:00am in the Tiki timezone and store that point in time (timestamp) in the database. When we index this data, we are again using Tiki timezone to convert the timestamp back to a date and store the actual date in the index (mysql and elastic work the same way here). So, we end up with different timezone handling for dates and dates with times - the former ones get stored as Tiki-defined timezone in the index while the latter get stored in UTC. Then, when we add range filters to search for these dates, we s
 tart to see the problems. What timestamps should we convert these dates to (Tiki-timezone ones if it is a date field or UTC if it is a date with time field)? If we have to get each field details when we search, it will eliminate quite the speed out of the search index. Even trackerlist or trackerfilter searches are unreliable as we use straight strtotime conversion not taking into account Tiki-defined timezone. So we end up with search results that sometimes work and sometimes do not work.
> 
> I think the root cause of the issue here is that we are treating dates as timestamp values (12am at a given timezone) and thus requiring a timezone to work correctly. Dates are really timezone independent in this context. When someone selects a date in a tracker field, they don't select a timezone. Oct 5 is Oct 5 no matter what timezone you live in (it is true that it happens in different points in time during the globe but it usually means the same for people all over the globe - the day that took place on Oct 5). So, my point is that we should get rid of all that timestamp conversions for date fields and store the dates as strings. We can keep dates with times as timestamps. If we use strings for dates, we will get reliable filtering, reliable storage in the database and also in the in
 dex. No time shifts based on timezones.
> 
> This change will require a migration of existing data and since existing data depends on Tiki-deifned timezone, migration should probably ask user to confirm if they recently changed their Tiki timezone. If you do so, you are risking of ruining your dates stored in tracker fields which is yet another problem with current implementation.
> 
> Since this letter got too long, let me summarize for impatient:
> 
> * tracker fields dates with times are entered in Tiki-defined timezone and stored as timestamps in db and UTC timezone datetime strings in the index
> * tracker date-only fields are stored in Tiki-defined timezone 12:00am in db and related date string representation in index
> * searching in Tiki and via the index is unreliable for date-only fields as range search filters define dates without timezone selection, so time-shifts happen
> * proposed solution is to store date-only fields as strings, so no shifts happen
> 
> Any objections to do this for Tiki 24?
> 
> Regards,
> Victor
> 
> 
> 
> _______________________________________________
> TikiWiki-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel
>