1

On 9.3.3, if one runs:

select EXTRACT(TIMEZONE FROM timestamp with time zone '1911-03-01 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1911-05-15 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1917-03-01 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1917-05-15 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1967-03-01 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1967-05-15 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1968-03-01 00:00 -8:00:00'), EXTRACT(TIMEZONE FROM timestamp with time zone '1968-05-15 00:00 -8:00:00');

One gets the following results:

0;0;
0;3600;
0;3600;
3600;3600

(The first time is the founding day of Las Vegas, the next few are some I used to debug the issue)

It seems there is no offset around 1911, an offset between 1911 and 1967 during summer but not winter and then always has one from 1968 onwards. This seems a little weird. Does anyone have any idea what is going on with the offsets here and whether this is expected behaviour or if there is something in my linux's setup that I could possibly change?

William Becker
  • 347
  • 2
  • 10
  • Which is your timezone? – leonbloy Apr 01 '14 at 19:37
  • I am in London, which just ticked over to daylight savings (hence me investigating timezones!). – William Becker Apr 01 '14 at 19:42
  • I'm not asking about yout "real" timezone, but about your configured timezone in postgresql (that influences the result). Run ` show timezone;` – leonbloy Apr 01 '14 at 19:43
  • "It seems there is no timestamp around 1911" What does that mean? What are you trying to do with those queries? – leonbloy Apr 01 '14 at 19:47
  • It is "GB". Sorry, that was mistyped - I should have said the timezone is 0 (I have corrected it) - but surely that only happens around 0 longitude? Is it the case that before 1911 the timezone everywhere was 0? Why then is it 3600 during May but 0 during March in 1921? – William Becker Apr 01 '14 at 19:53
  • Because (I guess) GB changed its timezone. But, again, I still don't know what you want to do. I suspect you don't understand what `extract(TIMEZONE` does, nor how Postgresql deals with timezones. Anyway, you queries won't tell you anything useful regarding Las Vegas, only regarding GB – leonbloy Apr 01 '14 at 19:56
  • What I'm trying to do is store the local time at Las Vegas on the day it was founded - from midnight to midnight at the local time there. To do this I'm using a timestamp with time zone - assuming that this stores the timezone of the timestamp in that local time. I am then trying to extract the timezone of that timestamp so I can view both the local time (sans timezone) and the UTC time. – William Becker Apr 01 '14 at 20:59
  • I expect the timezone I am extracting from the timestamp to be the -8:00:00 I am putting in, and it should be that no matter what the timezone of my current computer is, since "For timestamp with time zone, the internally stored value is always in UTC" – William Becker Apr 01 '14 at 21:05
  • 1) If you want to store the "local time there", then you do NOT want to store the timezone, just the "local time" (I guess you are misunderstanding the meaning of "local time") 2) If you want to store the FULL qualified (with timezone) instant, you can't; postgresql does not stores timestamps with timezones. See my answer here. http://stackoverflow.com/questions/6627289/what-is-the-most-recommended-way-to-store-time-in-postgresql-using-java/6627999#6627999 – leonbloy Apr 01 '14 at 21:07
  • 2
    "-8:00:00" is not a TIMEZONE! it's just an offset. – leonbloy Apr 01 '14 at 21:07
  • The documentation says for [extract timestamp](http://www.postgresql.org/docs/9.1/static/functions-datetime.html): "The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not handled.)" This should be what I want, no? – William Becker Apr 01 '14 at 21:07
  • Thanks @leonbloy - I think there is something I am still missing. You say that if you only have the timestamp with offset "you lose the timezone info, some calculations become difficult, and some impossible". Is it not enough to know the offset between the time and UTC? Why do you also need the offset? – William Becker Apr 01 '14 at 21:15
  • But thank you for pointing out the difference between timezone and offset - this was something I was conflating in my wording. – William Becker Apr 01 '14 at 21:18
  • 2
    [This related answer might help to understand.](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170) – Erwin Brandstetter Apr 02 '14 at 00:34

4 Answers4

3

Time zones change for all sorts of reasons.

Daylight savings rules change.

Sometimes timezone offsets change, too, if nations redefine their time zone for political reasons.

The canonical time zone information database is the tz or "zoneinfo" database, which used to be called the Olsen database. The zoneinfo DB is on the IANA site. There are a variety of programs to dump human readable versions of the DB.

You can use timestamp without time zone if you wish to store a particular moment in wall-clock time, without concern for time zone.

timestamp with time zone is sensitive to the system TimeZone setting on input and output, and is stored in UTC time as absolute seconds. So it's converted for input and output. If you want different conversions or to override the conversion you can use the AT TIME ZONE operator.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • So I can say: `select timestamp with time zone '1911-03-01 00:00 -8:00:00' at time zone 'UTC';` and this does correctly give me as a result `1911-03-01 08:00:00`. However I also want to extract the offset associated with the timestamp at that time. When I try it, as in the question, it gives me 0. How is the zoneinfo configured in such a way to do that? Doesn't [this](https://github.com/eggert/tz/blob/master/northamerica) say that it should be -7:52:58 at line 406? Otherwise, is it based on my local timezone's London time somehow? – William Becker Apr 01 '14 at 21:36
  • 1
    @WilliamBecker No, timestamps are *converted* on input. The TZ offset is not preserved, and there is no way to retain it and extract it later. If you wish to retain the TZ offset, you must store it separately. Yes, that's counter-intuitive. – Craig Ringer Apr 02 '14 at 00:36
1

The rules for your time zone are established by law, and the law changes.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Are these laws enumerated somewhere, so I can determine why it is behaving the way that it is? – William Becker Apr 01 '14 at 19:51
  • So here is a public repostiory of the zoneinfo db in human readable format: https://github.com/eggert/tz/blob/master/northamerica. Looking for Los Angeles, which deals with Pacific time, doens't mention why things change between 1911 and 1921 (look around line 400). – William Becker Apr 01 '14 at 20:51
  • @WilliamBecker, you are in "GB" time, not Pacific Time. If you want to tell PostgreSQL to act as if you were in a different zone, you have to tell it to do that. – jjanes Apr 01 '14 at 21:41
  • I don't. I just am confused how my local timezone would effect how the data saved in the `timestamp with time zone` when I am giving it an explicit offset. I thought that offset was relative to UTC, not my local time. It's a [ISO8601 offset](http://en.wikipedia.org/wiki/ISO_8601#Time_offsets_from_UTC), so should be from UTC, right? – William Becker Apr 01 '14 at 21:46
  • You are asking "At this given instant in time, what was the offset between UTC and my current time zone". You spelled the instant in time by using an offset, but that is not the offset you are asking postgres about. And it doesn't matter how you spell it, postgres collapses it to a canonical value. – jjanes Apr 01 '14 at 22:14
1

If you want to store the INSTANT at which the (local) clocks in Las Vegas marked 00:00:00 in the day in which the city was founded, and assuming that Las Vegas was using an offset of -8 hours, then you should store 1911-03-01 00:00 -8:00:00::timezonetx in a timezonetx field. Be aware, however, that what is really stored is only the "universal instant", when you read it you cannot know to which "local time at Las Vegas" it corresponds (unless you explicityl convert it, after reading it, to a timezone).

leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • I do want to do this, but if I run: `select EXTRACT(TIMEZONE FROM timestamp with time zone '1911-03-01 00:00 -8:00:00'), timestamp with time zone '1911-03-01 00:00 -8:00:00';` it gives me as a result: `0;"1911-03-01 08:00:00+00"` - it loses the timezone information, which is why I asked the question. How can I store this offset information in a `timestamp with time zone`? – William Becker Apr 01 '14 at 21:27
  • 1
    You can't in PG. The timestamp is ALWAYS stored without a timezone or offset (timezone or offset is used just for converting it for read or write). Yes, it's confusing, but datetime management is complex. Read my linked answer in the comments. – leonbloy Apr 01 '14 at 21:34
  • So, for my case, would I be best off always querying with `at time zone 'UTC'` so as to get one normalised time everywhere and storing the offset in another column to render local time? – William Becker Apr 01 '14 at 21:38
0

Depends on your timezone, really. In 1966, DST was first implemented nationally in the US to take place in 1967. States needed to pass laws to end it, meaning that in many areas, 1967 is the only year which used DST. This can lead to some very interesting glitches, where every date except those in 1967/68 behaves normally.

CSturgess
  • 1,547
  • 2
  • 13
  • 29