Re: storing TZ along timestamps
От | Jim Nasby |
---|---|
Тема | Re: storing TZ along timestamps |
Дата | |
Msg-id | C6631462-901B-4C26-8C71-D38C65865DB7@nasby.net обсуждение исходный текст |
Ответ на | Re: storing TZ along timestamps (Ian Caulfield <ian.caulfield@gmail.com>) |
Ответы |
Re: storing TZ along timestamps
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote: > On 19 July 2011 17:11, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >>>> Josh Berkus <josh@agliodbs.com> wrote: >>>>> The timestamp and the timezone in which that timestamp was >>>>> entered are two separate pieces of data and *ought* to be in two >>>>> separate fields. >>> >>>> So, if you're grabbing a timestamp and the time zone for it, how >>>> do you ensure you've done that atomically if you're at the >>>> boundary of a DST change? >>> >>> In my view of the world, the timezone that you are in is not an >>> object that changes across a DST boundary. >> >> You're right -- the moment in time should be fixed like in the >> current PostgreSQL "timestamp with time zone", and the time zone >> doesn't change with DST. Not an intentional read herring, but >> definitely some muddy thinking there. > > There was an earlier point made that if someone puts eg 5pm local time > two years in the future into the database, and then the DST boundary > gets moved subsequently, some applications would like the value to > still say 5pm local time, even though that means it now refers to a > different point in absolute time - this potentially seems like a > useful feature. Retroactive timezone changes wouldn't make a lot of > sense in this case though... Right; and timezone's aren't supposed to change retroactively. The ZIC database is specifically setup so that it knows thehistory of TZ changes and deals with the past correctly. > I guess there are three concepts of time here - an absolute fixed time > with no reference to a timezone, a time with a timezone that is still > set as a fixed point in time, or a local time in a specific timezone > that would move if the timezone definition changed. Or, another way to put the third class: a timestamp that remembers what it's original timezone was so that you can referto it a common timezone (such as UTC), OR you can refer to it at it's original, local time. That's our exact need forthis: we have different businesses that operate in different timezones. Generally, we only care about things in localtime, but there are cases (such as event logging) where we could care about local *OR* unified time. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
В списке pgsql-hackers по дате отправления: