On 21/11/17 18:31, Mark Rotteveel wrote: > On 2017-11-21 18:31, Lester Caine wrote: >> On 21/11/17 16:42, Mark Rotteveel wrote: >>> >>> If you need that, you also need to store the actual timezone somewhere. >>> PostgreSQL for example also uses an offset for timestamp with timezone >>> (not sure if that is required by SQL standard though). >> >> Firebird currently works nicely simply because you have UTC time and can >> add a second field for location which will provide ACCURATE details for >> displaying local time. Offset only timestamps are simply wrong for many >> reasons! But we need a working tzdist source network before anyone can >> reliably work with timezone data live. The fact that offsets change year >> on year make storing anything with an unqualified offset dangerous and >> the version of TZ data used to create the offset is as important as the >> offset itself. > > Except Firebird doesn't store a timestamp in UTC, Firebird stores a time > without any zone information, so current_timestamp depends on the > current timezone of the server, and values stored depend on the > interpretation (or assigned meaning) from the client application. In > other words, if not carefully controlled, it can become a mess with > varying interpretations without having a ground truth.
Which is why the sensible convention is to run the server as UTC if one has to manage data across multiple timezones. Trying to store 'server time' based data falls foul of DST twice a year. Even with a single timezone situation one still needs a coherent clock to manage things like railway timetables, so UTC timetable and Local display ensures there is no problem running services. ( Or the other approach British Rail used to use was never to run trains over night so there were no train movements between 1AM and 3AM ;) ) > A timestamp with timezone gives you more control, because you can still > interpret them in UTC. With the added benefit that a client can - if > necessary - correctly render those timestamps in its own timezone using > standard tools, or insert them with its current timezone offset, and > still have them correctly interpreted by clients using a different > timezone or normalized to UTC, etc. That is actually what PostgreSQL > does: a timestamp with timezone is normalized to UTC and stored in the > same format as a timestamp without timezone, and the offset is a config > or session-specific transformation, while a timestamp (without timezone) > would be not undergo such a transformation. Tikiwiki originally stored data using 'server time' and offset things back to UTC before displaying 'local time' ... except when an event was moved across a DST boundary on one or other clock the result was an hour out. The only way to make it work reliably was to ONLY store UTC based times and DISPLAY times based on the TZ data for the local client. And that location data has to be stored against the clients login data because you can't get it from the browser ... which was the part of the jigsaw that added to the confusion in the first place. Server time is simply local time display for the server location. >> That said, I've not seen what the current SQL standard is proposing, >> just what does NOT work when sourcing data from other databases. > > For the applications I work with, it suffices. I have been caught out a number of times in the past when meeting times were changed over a DST boundary or due to a short notice change of TZ data and NOT storing the information as to WHY a time is not UTC and WHAT was used to generate the stored offset simple creates a mess. Storing a simple static offset is not a solution to the problem of timezone offsets so can we not come up with a solution that has the potential to store real timezone offsets? -- Lester Caine - G8HFL ----------------------------- Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
