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

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