[csirac2]

    I strongly agree with the principles you chose - specificaly - all time storage and math should be done in UTC

I've had this argument many times before. Throwing away TZ is in fact throwing away important contextual information.

Having worked on a few data cleaning projects to re-purpose old scientific datasets, I can tell you that the quality of these data would have suffered markedly, and many corrections would not have been possible if the TZ/offsets had been discarded, Eg: "The geolocation for these records cross a TZ boundary here but the UTC offset doesn't reflect that until here so that explains the 1hr gap in the record where the user realized her mistake half-way into the new TZ so we should add an hour to these 860 records"

Yes, it would be better if raw data was recorded in UTC in the first place but when working with such data the ability to make decisions and inferences from the TZ is very useful.

[vbit]

> Yes, it would be better if raw data was recorded in UTC in the first place but when working with such data the ability to make decisions and inferences from the TZ is very useful.

I maintain that time and location are orthogonal. If the application needs both, store both - properly in separate fields. That's better than shoving the granular location in the form of a timezone in the time field.

[csirac2]

Yes, from a data modeler's perspective it is definitely orthogonal.

From an evidence-based perspective though, a full "chain of custody" has to be maintained (as far as alterations go), providence of information can be very important.

If you care about reproducibility, how raw data has been manipulated from the original, you'd better have some record of it.

For date-times almost all public datasets standardize on ISO8601. Any datetimes captured by persons or processes which aren't working in UTC from the very beginning will have the TZ offset in it.

Normalizing to UTC is up to the data consumer to do as they please, but some get very upset if you try to say that an event was recorded as happening at 03:00 if the person or process actually recorded 13:00+10:00. It may seem subtle and boring and pointless, but the fact is that you may be seen to be changing the facts.

[gpvos]

No.

It is in fact best to store the local time and the location (i.e., not "+01:00", but for example "Europe/Amsterdam"). Not UTC with or without time zone. For example, if you have an appointment in the future, and the time zone or daylight savings time rules change, this is the only way that the appointment can still be at the correct moment. And time zone rules do change.

And this way you can still represent absolute UTC time if you wish. With another representation, you cannot represent human-society time, which is often what you actually want.

[vbit]

Good point. However this is a specific use case requiring future local time, and as you mention, even TZ aware local time fails here. In this case you cannot come up with a guaranteed physical time in the future that the event will take place.

IOW, I think storing local time and location as a rule is not a good idea. The rule should be to use UTC and store location if needed. Only if that doesn't work (e.g. '5 minutes after the game is over') come up with some other representation.

[gpvos]

One extra thing (one could call it a weakness) with the local+location scheme is that you will need to have a way to distinguish the "repeated" hour after daylight savings time sets the clock backward one hour from the hour before it.

[lomnakkus]

The issue here seems to be two different definitions of time. There's "physical" time, i.e. a continuous line and there's human/calendar time which is different and is most definitely tied to a location. For an example, see http://en.wikipedia.org/wiki/February_30#Swedish_calendar . (No wonder that time zones complicate things enormously.)

[angersock]

Amen to this.

We've got a complete clusterfuck in our legacy code because somebody decided that UTC was not the right answer, nor was timezones quite, and so somehow made the heinous mistake of creating a "local UTC" and separately storing the TZ. The mind boggles.

I'm still straightening that mess out.

People, computers care not for your "calendar time"--usually, you want physical time and can fix the rest at the view level.