All vehicles went offline at 2025-03-09 02:00 with date truncation error...?

I was using my site about an hour ago, no issues. Just checked about ten minutes ago see the vehicles all missing. A couple of them can be clicked on and give invalid date.

Traccar log is now full of:

2025-03-08 21:41:10  WARN: Update device status error - Data truncation: Incorrect datetime value: '2025-03-09 02:41:10.515' for column 'lastupdate' at row 1 - MysqlDataTruncation (... < QueryBuilder:446 < DataManager:324 < DeviceManager:313 < ...)

Unlike the typical data truncation error caused by no gps data. these inserts actually have valid dates, not Jan 1st 1970.

Tried restarting, no help.

The data type for the tc_devices.lastupdate table is timestamp, so it should be able to handle that value.

But now... Explain this:

mysql> update tc_devices set lastupdate='2025-03-09 02:41:10' where id=2;
ERROR 1292 (22007): Incorrect datetime value: '2025-03-09 02:41:10' for column 'lastupdate' at row 1

mysql> update tc_devices set lastupdate='2025-03-09 01:41:10' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

All that changed was setting the value to 01:41:10 instead of 02:41:10

mysql> update tc_devices set lastupdate='2025-03-09 01:59:59' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tc_devices set lastupdate='2025-03-09 02:00:00' where id=2;
ERROR 1292 (22007): Incorrect datetime value: '2025-03-09 02:00:00' for column 'lastupdate' at row 1
mysql>

Anyone know what is happening?

I changed the tc_devices.lastupdate column from timestamp to datetime and that fixed it. I suspect there may be some timezone handling impact, so I'll keep an eye out for that.

Time "2025-03-09 02:41:10" (or anything between 2am and 3am) probably doesn't exist due to daylight savings time switch and that's what the database is complaining about.

ok. That makes sense, although it shows I have a second problem regarding timezones. Daylight Saving time starts at 2am in any local timezone, not 2am in UTC.

2025-03-09 02:41:10 does not exist in "local time" in areas that observe daylight saving time, but it DOES exist in UTC. Daylight savings time is a switch from one timezone to another. We switch from EST to EDT. The UK switches from GMT to BST.

Timestamp saves dates in UTC timezone, and I am in Eastern US time. As the database is trying to store the 2025-03-09 02:xx time it is first asking "Does this time exist because of daylight savings?" It should not be asking if that UTC time exists, it should be asking if that local time exists, which was 2025-03-08 21:xx

I have struggled with timezone on this app since day one and have made a few attempts to fix it. It reports everything in UTC even though my server is in Eastern. May be time to start from scratch with a new build and try to see where I went wrong first time.

We don't pass any timezone to the database, as far as I know. So the database is using whatever timezone it uses by default.

Understood, this is good information - it points me in the direction of database/os timezone handling first.

Thank you!

traccar-service.log entry from a few minutes ago:

2025-03-09 15:21:29 INFO: [Tc9162007] id: 8xxxxxxxx410, time: 2025-03-09 20:21:29, lat: 4x.xxxxx, lon: -8x.xxxxx, course: 356.0

15:21 is my current local time.
20:21 is 5 hours ahead - which it UTC+1

What setting would make the log show a 5 hour difference here?

Server is Centos Stream 8 set to New York timezone. Database is MySQL 8.0.20.

Log record time is your server time. And the position time is what device reported. If they don't match, it usually means some device or configuration issue.

The problem I am having seems to be the timezone offset being added twice.

2025-03-09 15:21:29 INFO: [Tc9162007] id: 8xxxxxxxx410, time: 2025-03-09 20:21:29, lat: 4x.xxxxx, lon: -8x.xxxxx, course: 356.0

20:21:29 is UTC. When "2025-03-09 20:21:29" is inserted into tc_postions.devicetime and fixtime, that is a timestamp field that stores as UTC - but it apparently thinks that "20:21:29" is local time and adds another 4 hours behind the scenes. So the date is visible as "20:21:29" to a select command, but is stored as 00:21:29. This can be proven by either casting the fixtime as UTC, or by altering the table to modify that column to datetime that does not store as UTC, where it suddenly now selects as "00:21:29".

The only configurations that make sense in traccar appear to be in the server config, attributes, timezone. Changing this and rebooting has no apparent effect.

Or I can use the settings for the device and set the timezone of the device to etc/utc.

If that doesn't work I'm just going to spin up a new server and built a new instance from scratch. I've made so many changes while trying to fix this that I no longer have any stable baseline for troubleshooting.

Waaait, is this it?

conf/traccar.xml:

<entry key='database.url'>jdbc:mysql://127.0.0.1:3306/traccar?serverTimezone=UTC&amp;...