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

Hi Bernard,

Range filters on dates will compare dates based on internal engine 
implementation but will always be correct (e.g. yesterday is equal to 
Oct 4 no matter if you use mysql or elastic).

The problem here is to get the right date for strings like 1 day to now 
and first day of this month. Since these strings are using PHP's 
strtotime function to convert to timestamp, we will need to use a 
timezone. We can default to Tiki-defined timezone here and allow 
timezone specification in the wiki syntax, so you can be flexible. At 
any rate, whenever you are searching, you need to make sure that range 
filters get converted to proper dates (i.e. the dates you really want).

Regards,
Victor

On 10/5/21 5:02 PM, Bernard Sfez wrote:
> Hello dear Victor and thank you for taking care of such things.
>
>> * proposed solution is to store date-only fields as strings, so no shifts happen
> Let say we store date-only fields as strings what will happen during range filtering. (In a plugin List or any) ?
>
> For exemple how you manage this to work:
>
> {filter range="tracker_field_covidHealthDeclarationDate" gap="1 day" to="now”} {* Within the last day *}
>
> Or like:
>
> {filter range="tracker_field_productsboughtDate" from="first day of this month midnight" to="first day of next month midnight”}
>
> Bernard
>
>
>> On 5 Oct 2021, at 16: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 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 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