Two legacy electricity meters write readings to meter_readings (seq, meter_id, local_ts, kwh). local_ts is a TIMESTAMP without a time zone: the meter's own clock in America/New_York. seq is the log's write order; for any one meter it increases with real time.
The data covers the night of 2024-11-03, when New York left daylight saving: at 02:00 EDT the clocks went back to 01:00 EST, so every local time from 01:00:00 to 01:59:59 happened twice — first in EDT (UTC−4), then in EST (UTC−5). Each meter's clock changed on its own, and you can see it in the data: the change is the first reading whose local_ts is earlier than that meter's previous reading.
Convert every reading to its UTC instant:
- •a reading between 01:00 and 01:59:59 local on 2024-11-03 is EDT if its meter hasn't gone back yet, and EST from the reading where it goes back onwards;
- •every other reading has only one possible instant.
Columns: meter_id, seq, local_ts, utc_ts (the instant, a TIMESTAMPTZ). Sort by meter_id, then seq.