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

Hi Jonny,

Answers inline:

On 10/5/21 6:21 PM, Jonny Bradley via TikiWiki-devel wrote:
> A little more on this (i'm afraid)
>
> I did some tests with my server_timezone set to various different places and that (luckily) makes no difference, probably because i have users_prefs_display_timezone set to local so it will try and use the browser's timezone.
>
> I tested on all day calendar events (repeating ones, non repeating ones seems to be for a random hour during the day dependent on when the event was added) and that are ok, start at 00:00:00 ending at 23:59:59 UTC but being displayed in my UTC+! zone as taking up two days.
V: We should check the UI method of entry of calendar events and fix 
that if you get mixed results based on browser timezone.
>
> However, on tracker items the stored timestamp for all day fields (JSCalendar ones) is set to 23:00:00 on the day before i set it to be, so not stored as UTC :(
V: I don't think they ever were stored as UTC. More on this below.
>
> And when i changed my desktop timezone (to Montreal coincidentally) and re-edited the same tracker item it changed the saved timestamp to 4am on that day.
V: It is important to note if this is a date-only field or a date and 
time field. For date-only fields, I think this is the problem you 
experience here - it is the same as the one I described - time is 
shifted based on different timezone configurations.
>
> So i think (i'm afraid) the tracker dates are being saved as locally offset timestamps, but without a record of which timezone that was.

V: Date fields with time allow you to select the exact time and also 
display which timezone that time is - it is by default the Tiki defined 
timezone in Admin settings. When a timestamp is sent back to the 
backend, it is offset by the browser timezone offset (as the datepicker 
uses browser-based js date object to make calculations) and also the 
Tiki-based timezone, so we end up with a timestamp saved in Tiki-db 
which should correspond 100% to the one displayed to the user (again, in 
Tiki-based timezone).

Unfortunately, we do the same for date fields without times, so we are 
saving 12am on the target date of the Tiki-defined timezone. This is not 
locally offset timestamps - we know the timezone and it is the 
Tiki-defined one. If that gets changed, we will end up with possibly 
corrupt dates in the database.

>
> So surely the fix is to use UTC timestamps only? And maybe ignore the timezone when rendering just a date for display?
V: Unfortunately, no. If we continue to keep timestamps as date storage 
mechanism and try to store the times as 12am UTC, then this gives us a 
date shift for timezones before UTC - i.e. everything to the west of UK. 
E.g. you pick Oct 6 as a date. Oct 6 12:00am UTC is 1633478400 as a 
timestamp. This timestamp in New York time, for example, is Oct 5 
8:00pm. So, if Tiki timezone is set to America/New_York, you will get a 
date shift even if we store dates in UTC formatted timestamp... that's 
why I provided a fix in 2017 to convert dates to Tiki-defined timezone 
12:00am timestamp and store like that.
>
> I can't see how we can fix existing data, because there's no way (i think) of telling which timezone it was entered from (if we're using detect timezone).
V: We can convert back the existing timestamps to dates based on 
Tiki-defined timezone. This will work unless admin has changed 
Tiki-defined timezone in the past 3-4 years... Please check my answers 
to your other email below as well...
>
> Or am i totally lost? 😂🤓
>
> jonny
>
>
>
>
>
>> On 5 Oct 2021, at 15:33, Jonny Bradley via TikiWiki-devel <[email protected]> wrote:
>>
>> 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?
V: That's right, unix timestamp doens't have a timezone but is correctly 
specifying a point in time which is different time in different 
timezones. I have checked this logic extensively the past several days 
and can confirm that dates chosen by the user are offset by the 
Tiki-defined timezone. I explained the reasons above.
>> 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?
V: For times yes, but not for dates. I explained above...
>>
>> 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.
V: Again no, we have no consistency here. On most places, we store and 
use dates with times which is fine to continue storing as a timestamp. I 
was talking about date-only fields which are rare but the most common 
use-case is tracker "date and time" field with date-only option 
selected. I am not sure we want to mix this with other places in the 
system that doesn't do any timezone conversions. Maybe only the calendar 
events that have date-only selection - they might be a good candidate to 
unify with these tracker fields.
>>
>> 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.
V: I agree that changing the datetime format currently is not needed - 
it is just a relatively big effort without any gain besides consistency 
of storage. Dates, however, need the change as the current storage 
method just leads to troubles with all those conversions. Furthermore, 
it is rather illogical to store a date as a timestamp. Mysql doesn't do 
this, Elastic doesn't do this, why should we do it? It is common for one 
field type to store data in one way and another field type to store data 
in another way. At the end, they all get stored in a text field in the 
database, so it is up to us how we interpret that data.
>>
>> 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
V: Yes, I have thought about this as well but seems rather hacky, indeed.
>>
>> 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!
V: Sure, we can agree on something off the list.
>>
>> 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.
V: This is because Tiki uses time conversion functions without setting 
the proper timezone - thus using your server-defined one.
>>
>> 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!? ;)

V: I haven't recently checked how calendar event times are stored in db 
but they should really follow tracker date and time fields. If you 
create an event and define a start/end time in a chosen timezone (e.g. 
the one Tiki is in), then the timestamps saved in db should correspond 
to that time. For all-day events, we again face the same problem we 
discussed above - 12:00am time is shifted based on timezone 
configuration which shouldn't happen.


Regards,
Victor

>>
>>
>>> 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 timezone, 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 start 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 whe
>> n 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 index. 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
>>>
>>
>>
>> _______________________________________________
>> TikiWiki-devel mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel
>>
>
>
> _______________________________________________
> TikiWiki-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel



_______________________________________________
TikiWiki-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel