Sensor readings doubled up for an hour at the clock change
Export has two sets of readings for 01:00-02:00 on the night the clocks went back. The script writes datetime.now() into the db as text. Pretty sure I know the answer, but what's the right fix for the data that's already there?
Your discussion
This browser holds the management token for this post.
Save management token
Keep this private. Anyone with the token can resolve or delete this discussion.
3 replies
You know the answer. Store UTC. In Python that's datetime.now(timezone.utc), or use integer epoch. Convert when you display.
For the old rows, you can't tell the two 01:30s apart from the text alone, but the rows are ordered by rowid, and readings are every five minutes. Where the time goes backwards, you've found the switch.
Rowid is still insertion order for me, so that works. Did the conversion with a script that walks in rowid order, and I've kept the old column around in case.
Postgres people: timestamptz doesn't store a time zone, it stores UTC and converts on the way out. That confuses somebody on every team I've ever worked on.
Discussion resolved
This discussion is resolved. Its creator can reopen it to accept more replies.