Hacker News Reader

> So if a policy starts at 00:00:00 and ends at 23:59:59, that's the full day, all the way up to midnight.

If you had a policy for the calendar year 2016 would it not have covered 31 December 2016, 23:59:60 then?

https://www.timeanddate.com/time/leap-seconds-background.htm...

Claims are lodged by humans. I doubt any human would report they had a loss at 23:59:60 hehe!

> I doubt any human

Never trust user input though, of course :P

What if the loss was caused by and at the leap second? e.g. imagine a self-driving car that ignored input during the leap second and crashed into a lane divider? You may have proof that the accident occurred at exactly 23:59:60.

What if the car is flying through the air aside a suspension bridge at 23:59:59 and :60, and crashes into the water a second or two later? Has the loss occurred the moment the impact with terrain becomes inevitable, or not until impact?

Exactly. It's usually best to specify end times exclusively, and not just for leap seconds; it's easier to program in general. If your clock has sub-second granularity, then you can end up with a time value like 23:59:59.123. Which will require some truncation before it can be compared like t ≤ 23:59:59. If you use exclusive end times, you can do the comparison directly; 2016-12-31 23:59:59.123 < 2017-01-01 00:00:00. The key is that now we have an end date-time that actually specifies a true instant in time, and isn't an interval in and of itself.

For that very reason, for start/end policy times, we store them aligned to the second - no fractional seconds allowed. There'd be no legal basis for that anyway.

Obviously for actual timestamps - e.g. created at/updated at times - we do store a point-in-time with fractional seconds.

Very fair point.

From an operational perspective, if there was an incident in the second between 23:59:60 and midnight, it would of course be covered - irrespective of what's in any system, written on any doc, displayed in the app, etc.

We don't worry too much about leap seconds - particularly as very few people are even likely to realise it was a factor, and the national Motor Insurance Database (MID) does not support them.

The MID doesn't even allow us to specify timezones, so every year we sell around 20-30 one-hour car insurance policies which appear to run for 1 second - displaying the same start and end time on police computers etc. (Due to the clocks going back from BST to GMT.)

From a legal perspective, the time written on the policy doc is inclusive - we can't just decide that we want it to be exclusive because it suits our purposes.

> From a legal perspective, the time written on the policy doc is inclusive - we can't just decide that we want it to be exclusive because it suits our purposes.

Well, apparently it's not if it's understood that it covers leap seconds as well. With regard to the internal modeling, I always go for half-open intervals, where the open side depends on the data being modeled, they have much nicer mathematical properties than closed intervals.

Why not:

valid_from: 2019-01-01T00:00:00+x

valid_until: 2019-01-02T00:00:00+x

Even 23:59:59.9999999 would still be < valid_until without any special tricks.

I bet you already know this but for the reader: the solution is to not think of times occurring with an offset but to rely on the IANA timezone database to figure out what a timezone means at a particular UTC time: https://www.iana.org/time-zones

A question to both parent of this and this specifically.

How would you deal with the current EU situation? They have recently voted to stop yearly transitions to summer time. But there must still be a timezone assigned to an event.

Are you assigning timezones (in the certificates themselves) as the timezones of a particular geographic location,, rather than as time offsets?

On the certificates, we'd state e.g. "this policy runs from 27th March 2019 at 16:52:12 (GMT) to 3rd April 2019 at 17:52:12 (BST)" for a 168-hour (~7 day) policy.

Logically, we think of it as the "wall clock" time, plus the location ("Europe/London" - we don't operate outside the UK yet), plus the local zone (BST/GMT) to handle ambiguity when clocks go back.

Practically, right now the start/end date-times are stored using a Postgres timestamp with no timezone. As we've never issued an individual policy of more than 28 days' duration or more than 7 days in advance, we've never actually had to deal with any situation where the point-in-time is no longer correct. And this case is not likely to happen without a decent amount of notice.

We're very much in favour of abolishing seasonal transitions though - causes lots of confusion.

> And this case is not likely to happen without a decent amount of notice.

In the jurisdictions you care about. Last-minute timezone changes do happen, and the main time zone database has been updated retroactively in the past. It's actually possible that a timezone change has even been enacted retroactively (thanks to countries that like doing very last-minute DST starts).

Yeah indeed - for now we're fortunate that it's not something we need to worry about actually handling perfectly. It's more something we like to think about at this point.

One day we will need to deal with it and conversations like these are super helpful to support that.

If a time is designated as "local" it is most flexible to store the location in a way that will handle the dynamism. e.g. I would not record a time as "16:00 BST" but rather "16:00 Europe/London" and use the olson timezone db to resolve it to BST or GMT downstream.

You should definitely use geographic timezone like America/Los_Angeles instead of just offset. Offset actually isn't a real timezone it is just an offset. Timezone usually means a lookup table of what offset it has at what time and that can also change due to different laws at different time. So I assume the moment EU countries will change the rules there will be one more row for each timezone that will say "from this time the offset is such and such". That means it is also important to use the right timezone in the sense that even though Bratislava/SK and Prague/CZ have the same rules (DST time and offsets) at the moment they can change in different ways after the DST is removed (as whether to use winter or summer time afterwards is in jurisdiction of each country I think).

I agree. In the only project I worked on where time was really important I stored a triple (original datetime, UTC timestamp, original time zone). I used UTC to globally sort events and sent the original datetime back to clients for display.

On the other side, sometimes we don't know where an event happens. Think of GPS trackers close to a border which is also a timezone border. This is a more complex problem which requires at least a model of country borders and maybe roads.

Finally, on some systems I'm using as a user, they have some EU timezones but not even all the major countries. It doesn't matter now but it will. I expect they'll add the missing timezones and make us confirm where we are.

That doesn't solve the problem. Suppose Massachusetts were to decide to move from the Eastern to the Atlantic time zone and New York were to decide to remain in the Eastern time zone. The time zone you would need for people in Massachusetts would be America/Boston, not America/New_York--which doesn't exist now.

Representing future times correctly is very hard, especially because time zone changes (including creation of new ones!) aren't always announced years in advance.

None of these tricks will help if I’m insuring myself for international travel. Only the trip to and from the airport is in the time zone of the policy.

Any incidents along the way or after that are going to need an interpretation relative to UTC.

Thanks for mentioning this. I came across this page which discusses the theory and practice of timezone databases. https://data.iana.org/time-zones/theory.html