Description
Originally brought up in #761.
We (unintentionally) took the bad decision to use timestamp columns in Postgres for storing models.CustomTime values instead of timestamptz. This is the cause of some very fundamental and severe bugs that - surprisingly - were revealed only now.
The timestamp type apparently behaves in way that any timezone information is simply "chopped off" a date when inserting it to the database, without properly converting it to UTC or some other zone before. For example, if Wakapi runs in CEST and attempts to store 2025-03-25 14:00:00 +02:00 as a date, it will simply end up as 2025-03-25 14:00:00 in the database. However, when retrieving it back, it is (other than for timestamptz) interpreted as UTC and thus become 2025-03-25 14:00:00 +00:00 (aka. 2025-03-25 16:00:00 +02:00). Consequently, for servers running in the eastern hemisphere, all dates will be artificially ported to the future, while for timezones corresponding to the western hemisphere, all dates are shifted to the past.
A solution would be to migrate to timestamptz (precisely here), including to migrate all existing data. The big problem here is that timezone information has already been lost the moment the dates where inserted into a timestamp column. Accordingly, we can only make a guess about what the original timezone was, that is, what timezone the dates must be interpreted in. The best assumption would be the Local zone of the environment where the Wakapi server runs in (e.g. UTC inside Docker (unless TZ is explicitly set) or the zone of your host system otherwise (check date +%Z)). However, if the server's timezone has changed at some point in the past for whatever reason (e.g. you migrated from Docker to native or you actually migrated to a server located in a different timezone), there is no way to "losslessly" reconstruct your heartbeats' dates, unfortunately.
For now, 14fae4a introduced a workaround, which effectively does the same thing as described above in a hacky way at "runtime", but that's not a permanent solution. In the long run, we need this migration, so that dates are stored properly in the future.
Steps to be done:
- Remove hack introduced in Failed to merge durations due to overlap #761
- Change type of
models.CustomTimetotimestamptz - Write migrations for all tables that "reinterpret" the dates, e.g.:
Note: We'd also have to account for daylight saving time, i.e. different offset intervals depending on whether DST was in place at the creation time of a heartbeat (or anything else).
update heartbeats set time = time - interval '1 hour'; --- assuming original dates were in CET
Help needed:
To everyone running Wakapi with Postgres: can you please checkoout the latest commit and test if stuff remains working for you in production? I'm reluctant to push a new release just yet.