Migrating from local Docker and PostGres (hosted on unRAID) to AWS - timezone weirdness
Have you checked in the backup what format the time was stored?
All the timestamps in the source database are in AEDT, which seems odd. The backup is just an psql dump, and the timestamps are in timestamp without timezone as per the schema:
COPY public.tc_events (id, type, eventtime, deviceid, positionid, geofenceid, attributes, maintenanceid) FROM stdin;
1 deviceOnline 2024-10-29 20:27:40.878 3 \N \N {} \N
2 deviceOnline 2024-10-29 20:28:00.672 1 \N \N {} \N
...
41831 deviceOnline 2025-02-22 21:31:20.143 13 \N \N {} \N
41832 deviceOnline 2025-02-22 21:31:43.685 4 \N \N {} \N21:31 local time is just before the old server was stopped and the backup taken.
as far as I can see, though, traccar expects all times to be in UTC, which makes sense. we can update the timestamps so they are in UTC, and everything works as expected.
so the question really is, how is it working on the old server when the timestamps are in local time (AEDT)?
It probably works because timezone of the server matches timezone of the database.
Yeah, that's what I suspected, though oddly, the time zone in the source docker container is UTC (the upstream docker hub container image has no timezone data installed).
I'm sure there is more going on here, but no matter - timestamps in the database being UTC makes more sense, anyway, given users could be anywhere.
Our existing service is installed on an unRAID server, with separate Docker Containers for Traccar (6.6) and PostgreSQL. We just attempted to move our service to Amazon EC2 (running Docker, for Traccar 6.6) and Amazon RDS (for PostgreSQL), and all the timestamps are reported 11 hours ahead.
For the local install on unRAID, all of the timestamps in the database locally have been written - and I don't know why - as local time (AEDT). The unRAID server is set to AEDT. The Docker Container for my local instance is UTC. The PostgreSQL container is set to AEDT. These were all defaults.
The Traccar server portal has the time zone set to Australia/Melbourne. Everything here works, with no issues.
When we backed up the database and imported it into Amazon RDS, and installed Traccar in Docker on our EC2 instance (with our traccar.xml file), Traccar suddenly showed all time stamps as 11 hours in the future. All device statuses were set to "in XX hours". As vehicles were driving around and uploading their records, their data was being imported into the database in raw UTC now, so the web portal did not update any real-time data because, as far as it was concerned, these were historical records, because they are older than the "latest" record, which appears to be in the future.
I don't get this. Why did Traccar (installed initially on version 6.5) store everything in local time but not have any issues even if the time zone in the web portal is set to Australia/Melbourne? Shouldn't it have gotten completely confused?