storing TZ along timestamps

On May 27, 2011, at 1:43 PM, Alvaro Herrera wrote:

Right now we rely on the tzdata files on disk for things like
pg_timezone_names and other accesses of TZ data; so the files are the
authoritative source of TZ info. So we need to ensure that whenever the
files are updated, the catalogs are updated as well.
I think we could make this work if we "refreshed" the catalog from the
files on SIGHUP if the directory changes (say, a new timezone is
created). Note that I am currently proposing to store only the zone
names in the catalog, not the full TZ data.

Are there objections to the general idea? If not, I'll flesh a more
complete proposal.

I like it, but what do you do when a TZ has been renamed or has ceased to exist. Or, worse, existed last week, so last week's dates might still use it, but next week's must not?

Best,

David

Alvaro Herrera <alvherre@commandprompt.com> writes:

Right now we rely on the tzdata files on disk for things like
pg_timezone_names and other accesses of TZ data; so the files are the
authoritative source of TZ info. So we need to ensure that whenever the
files are updated, the catalogs are updated as well.
I think we could make this work if we "refreshed" the catalog from the
files on SIGHUP if the directory changes (say, a new timezone is
created).

(1) SIGHUP processing normally occurs outside any transaction.

(2) The only obvious way to ensure the "refresh" is done once, and not
once per backend, is to have the postmaster do it ... which is a
nonstarter for many reasons.

I'd suggest instead considering something like the pg_collations
approach: load up the catalog once at initdb. If the user really needs
to add to the set of accessible TZ names later, give him a tool to do
that. But it's 100% not worth either the implementation pain or the
cycles to try to auto-update the catalog, especially not as often as
once per SIGHUP.

BTW, what will you do about pg_upgrade? Ensuring the OID mapping
doesn't change seems like loads of fun.

regards, tom lane

"David E. Wheeler" <david@kineticode.com> wrote:

I like it, but what do you do when a TZ has been renamed or has
ceased to exist. Or, worse, existed last week, so last week's
dates might still use it, but next week's must not?

I think the key thing is that the timestamp portion of it would be
identical to our current TIMESTAMP WITH TIME ZONE -- always store it
in the value UTC zone. That way comparisons and math between
timestamps could remain sane. The stored time zone portion would be
what it would be the display format, if usable. In an extreme
situation like you describe above, I guess you could fall back on
what we do now for display of a timestamptz value.

Personally, I think it would be good to move a bit closer to the
standard by including a time zone in a TIMESTAMP WITH TIME ZONE
value. The biggest problem I can see is how to try to do this in a
standard conforming fashion without breaking existing code. It
would seem more than a little odd to support the standard semantics
with nonstandard syntax and vice versa.

-Kevin

"David E. Wheeler" <david@kineticode.com> writes:

I like it, but what do you do when a TZ has been renamed or has ceased
to exist.

As far as that goes, I think "nothing" is a sufficient answer. There's
no requirement that an OID in the mapping table correspond to a live TZ.
It's just a more compact way of storing a string name.

regards, tom lane

On May 27, 2011, at 2:35 PM, Tom Lane wrote:

"David E. Wheeler" <david@kineticode.com> writes:

I like it, but what do you do when a TZ has been renamed or has ceased
to exist.

As far as that goes, I think "nothing" is a sufficient answer. There's
no requirement that an OID in the mapping table correspond to a live TZ.
It's just a more compact way of storing a string name.

Well then you'd just want to be sure to never delete TZs.

I think the issue of trying this week to use a TZ that was removed last week might be more problematic. I mean, we could just let the user use it, but that hardly seems wise…

Best,

David

On Fri, May 27, 2011 at 2:32 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

I think the key thing is that the timestamp portion of it would be
identical to our current TIMESTAMP WITH TIME ZONE -- always store it
in the value UTC zone.

Fwiw our timestamp with time zone stores seconds since the epoch. This
is a quantity which is independent of timezones entirely. Excluding
relativistic effects there have been the same number of time zones
since that point in time regardless of where you stand relative to the
sun.

My question for Alvarro is whether he really wants the text label for
the time zone at all, or just the offset which was used to enter it.
That is, if I enter "12:00pm" with my current time zone set to GMT and
later update the tzdata on the machine to start summer time on a
earlier date should the data type now show "1:00pm BST" or should it
still display "12:00pm +000" and leave it up to the reader to decide
whether why I entered it in a weird time zone for that time of year?

--
greg

On 05/27/2011 01:43 PM, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I am very interested in the use-case for this (in part as I'm working on
a PG related time talk). My experience thus far is that people who want
this do not fully understand the nature of date-time calculations and
variables in PG.

It is currently possible to store a TZ in a separate column, but this is
a bit wasteful and not very convenient anyway.

Are there objections to the general idea? If not, I'll flesh a more
complete proposal.

I'm not crazy about it.

Although time-stamp-with-time-zone is, perhaps, a bad name for what is
actually a "point in time", a point-in-time is what timestamptz
represents. I can enter it and allow my defaults to take over, specify
abbreviations, explicit offsets or long names none of which change the
actual point in time. Likewise, I can display said point-in-time in any
of dozens of ways according to my needs.

steve=# select '2011-05-27 12:34'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34-07'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34 PDT'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 11:34 PST'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 15:34 US/Eastern'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

select now() - '02:58:54.605041'::interval;
?column?
-------------------------------
2011-05-27 12:34:00.394959-07

Granted, I'm a random sample of 1, but I've never found anyone with a
real need for this feature - especially since the capability already
exists to achieve the requested result, and much more flexibly, by
either a separate column or a user-defined type.

Questions:

What would be the storage impact (tables, indexes and backups) for those
of use with tens-of-millions of pieces of timestamp data?

What type of timestamp would be stored? Abbreviated/offset (PST, -07),
full (US/Eastern) or a mix? Is there an expectation that the stored time
zone information would be used for any calculation purposes? If so, how
would rules be applied? Would there be any form of error-checking?
Currently PG accepts non-existent time zones but maps them to UTC:

steve=# select '2011-05-27 15:34'::timestamptz at time zone 'US/f00';
timezone
---------------------
2011-05-27 15:34:00

Would there be any impact to existing queries?

How would dump/restore issues be handled - especially if the time-zone
info changes in between?

More as I think of them.

Cheers,
Steve

On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I am very interested in the use-case for this (in part as I'm working on a
PG related time talk). My experience thus far is that people who want this
do not fully understand the nature of date-time calculations and variables
in PG.

The use cases I recall having been mentioned in the past were accurate
data retention and calendaring applications.

Accurate data retention for things like drug trials need to guarantee
they retain precisely what the user entered, not an equivalent value.
If you run a report on a drug trial you need to see that the event was
recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
to run the report in London.

And calendaring apps want to know what timezone is attached to an
event, not only the point in time at which it occurs. If your plane
flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
that to book your taxi at 2:30pm EST -- not 7:30pm GMT.

Both of these two cases can be handled differently. The former by
storing the raw text inputs and then storing the interpreted value as
a derived column separetly, and the latter by storing the local time
zone to use for display as an additional attribute along with the
local address and other attributes of the calendar event.

--
greg

On 05/27/2011 04:29 PM, Greg Stark wrote:

On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I am very interested in the use-case for this (in part as I'm working on a
PG related time talk). My experience thus far is that people who want this
do not fully understand the nature of date-time calculations and variables
in PG.

The use cases I recall having been mentioned in the past were accurate
data retention and calendaring applications.

Accurate data retention for things like drug trials need to guarantee
they retain precisely what the user entered, not an equivalent value.
If you run a report on a drug trial you need to see that the event was
recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
to run the report in London.

And calendaring apps want to know what timezone is attached to an
event, not only the point in time at which it occurs. If your plane
flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
that to book your taxi at 2:30pm EST -- not 7:30pm GMT.

Both of these two cases can be handled differently. The former by
storing the raw text inputs and then storing the interpreted value as
a derived column separetly, and the latter by storing the local time
zone to use for display as an additional attribute along with the
local address and other attributes of the calendar event.

So the proposed change does not handle the first case as you need to
capture the raw input.

And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I just
ask for the data in that zone. Altering the data type gains nothing.

Cheers,
Steve

On May 27, 2011, at 6:29 PM, Greg Stark wrote:

Both of these two cases can be handled differently. The former by
storing the raw text inputs and then storing the interpreted value as
a derived column separetly, and the latter by storing the local time
zone to use for display as an additional attribute along with the
local address and other attributes of the calendar event.

Which means you're back to a very cumbersome method that involves another field. That's a tremendous amount of extra code.

We run multiple businesses around the globe. Each business operates in it's own timezone, and 90% of the time we want things handled in that timezone. The wheels fall off the wagon if we try and combine data from multiple locations into a single database; there's no reasonable way to say: give me the data in this field *at the timezone that was originally entered*, except for not storing timezone data at all. If we don't store timezone data at all, then it's impossible to determine an actual point in time that something happened at.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, May 27, 2011 at 04:43:28PM -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I've felt that pain here and there too...

So the first thing is cataloguing timezone names, and assigning an ID to
each (maybe an OID). If we do that, then we can store the OID of the
timezone name along the int64/float8 of the actual timestamp value.

Right now we rely on the tzdata files on disk for things like
pg_timezone_names and other accesses of TZ data; so the files are the
authoritative source of TZ info. So we need to ensure that whenever the
files are updated, the catalogs are updated as well.

Problem with this approach (mapping external time zone names to OIDs)
is: dump/restore would only be meaningful if you "carry over" the time
zone data, right?

That is: two independent systems are likely to have different mappings
(even if at some point they have the "same" TZ data?)

What would be a solution to that?

(a) A central, "official" catalog, with only additions, never deletions
(perhaps with some space carved out for "local" additions, to minimize
conflicts)?
(b) A hash of the time zone name?

Both not very good ideas, I know. Although (a) might be less bad than it
seems. Most Unixoids (including OSX) seem to have basically Olson's.
Don't know about Windows, but it might seem feasible to make some
mapping (or union). Only important rule: no backtrack :-)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFN4JWtBcgs9XrR2kYRAt+mAJ0atx3u6pll50+s4vVwCKZUjqmnSQCffWNe
gzSFgRCFUvsd8pbH1Qm/ho4=
=FVhO
-----END PGP SIGNATURE-----

On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:

And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you
retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I
just
ask for the data in that zone. Altering the data type gains nothing.

How about a recurring appointment that happens every Tuesday whenever it
is 9:00am in California, independent of DST (in California or where ever
the participant actually is). I'm not sure how to solve that within the
SQL framework. You might need to use time with time zone with a
placeholder timezone, and then a rule that date + time with time zone
creates a timestamp with time zone that resolves the time zone for that
particular day.

On 05/28/2011 02:58 PM, Peter Eisentraut wrote:

On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:

And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you
retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I
just
ask for the data in that zone. Altering the data type gains nothing.

How about a recurring appointment that happens every Tuesday whenever it
is 9:00am in California, independent of DST (in California or where ever
the participant actually is). I'm not sure how to solve that within the
SQL framework. You might need to use time with time zone with a
placeholder timezone, and then a rule that date + time with time zone
creates a timestamp with time zone that resolves the time zone for that
particular day.

Interval math is pretty smart about that:

select '2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60));
------------------------
2011-06-07 09:00:00-07
2011-06-14 09:00:00-07
2011-06-21 09:00:00-07
2011-06-28 09:00:00-07
2011-07-05 09:00:00-07
2011-07-12 09:00:00-07
2011-07-19 09:00:00-07
2011-07-26 09:00:00-07
2011-08-02 09:00:00-07
2011-08-09 09:00:00-07
2011-08-16 09:00:00-07
2011-08-23 09:00:00-07
2011-08-30 09:00:00-07
2011-09-06 09:00:00-07
2011-09-13 09:00:00-07
2011-09-20 09:00:00-07
2011-09-27 09:00:00-07
2011-10-04 09:00:00-07
2011-10-11 09:00:00-07
2011-10-18 09:00:00-07
2011-10-25 09:00:00-07
2011-11-01 09:00:00-07
2011-11-08 09:00:00-08
2011-11-15 09:00:00-08
2011-11-22 09:00:00-08
2011-11-29 09:00:00-08
2011-12-06 09:00:00-08
2011-12-13 09:00:00-08
2011-12-20 09:00:00-08
2011-12-27 09:00:00-08
2012-01-03 09:00:00-08
2012-01-10 09:00:00-08
2012-01-17 09:00:00-08
2012-01-24 09:00:00-08
2012-01-31 09:00:00-08
2012-02-07 09:00:00-08
2012-02-14 09:00:00-08
2012-02-21 09:00:00-08
2012-02-28 09:00:00-08
2012-03-06 09:00:00-08
2012-03-13 09:00:00-07
2012-03-20 09:00:00-07
2012-03-27 09:00:00-07
2012-04-03 09:00:00-07
2012-04-10 09:00:00-07
2012-04-17 09:00:00-07
2012-04-24 09:00:00-07
2012-05-01 09:00:00-07
2012-05-08 09:00:00-07
2012-05-15 09:00:00-07
2012-05-22 09:00:00-07
2012-05-29 09:00:00-07
...

Or if you have to call in from London (notice the blips between 4pm and
5pm due to London and California switching to/from DST on different dates):

select ('2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60))) at time zone 'Europe/London';
---------------------
2011-06-07 17:00:00
2011-06-14 17:00:00
2011-06-21 17:00:00
2011-06-28 17:00:00
2011-07-05 17:00:00
2011-07-12 17:00:00
2011-07-19 17:00:00
2011-07-26 17:00:00
2011-08-02 17:00:00
2011-08-09 17:00:00
2011-08-16 17:00:00
2011-08-23 17:00:00
2011-08-30 17:00:00
2011-09-06 17:00:00
2011-09-13 17:00:00
2011-09-20 17:00:00
2011-09-27 17:00:00
2011-10-04 17:00:00
2011-10-11 17:00:00
2011-10-18 17:00:00
2011-10-25 17:00:00
2011-11-01 16:00:00
2011-11-08 17:00:00
2011-11-15 17:00:00
2011-11-22 17:00:00
2011-11-29 17:00:00
2011-12-06 17:00:00
2011-12-13 17:00:00
2011-12-20 17:00:00
2011-12-27 17:00:00
2012-01-03 17:00:00
2012-01-10 17:00:00
2012-01-17 17:00:00
2012-01-24 17:00:00
2012-01-31 17:00:00
2012-02-07 17:00:00
2012-02-14 17:00:00
2012-02-21 17:00:00
2012-02-28 17:00:00
2012-03-06 17:00:00
2012-03-13 16:00:00
2012-03-20 16:00:00
2012-03-27 17:00:00
2012-04-03 17:00:00
2012-04-10 17:00:00
2012-04-17 17:00:00
2012-04-24 17:00:00
2012-05-01 17:00:00
2012-05-08 17:00:00
...

Cheers,
Steve

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Regards,
Jeff Davis

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

why not use a composite type for that? performance maybe?

merlin

Excerpts from Merlin Moncure's message of mié jun 01 21:36:32 -0400 2011:

On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

why not use a composite type for that? performance maybe?

To avoid having to implement all the operators and lookup tables (of
timezones) in userland, mainly. Probably performance would be affected
too, not sure, but that's not the main point.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 06/01/2011 05:18 PM, Alvaro Herrera wrote:

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

That eliminates many of my issues - I just didn't want the type changed
underneath me. But some considerations remain - including some new that
have crossed my mind:

1. How would the time-zone be defined in this composite? Offset from
GMT? Timezone (well, link thereto) with all DST rules intact? Would
"extract" need to be modified to include the ability to grab the timezone?

2. What would be the precedence for defining originating timezone?
Default? Set timezone to? ...at time zone...? Based on the timestamp
(2011-06-02 12:34:56-07)?

3. Would indexing/sorting include the originating zone? If so, how would
time zones collate (base offset, actual offset based on the timestamp,
name)?

4. What would be the corresponding type when used with
Perl/PHP/Python/... applications - would they require special
non-standard handling?

Since this isn't going to alter my current beloved timestamptz and I
don't have a use-case I leave the decisions on the above to others. But
in my imagined use-cases I still see the originating zone as a separate
piece of information better handled as a different column - for example
sorting by timestamp plus priority or selecting everything for a
specific time zone.

Cheers,
Steve

On Thu, Jun 2, 2011 at 12:55 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

why not use a composite type for that?  performance maybe?

To avoid having to implement all the operators and lookup tables (of
timezones) in userland, mainly.  Probably performance would be affected
too, not sure, but that's not the main point.

right -- I see where you are going with this. ok, some random questions:
*) what about making a 'timezone' type in addition to (or even instead
of) the timezonetz_inputtz? Then you could in theory treat the your
proposed type as a composite of timezonetz and timezone, just as
timestamptz is a 'composite' of date and timetz. (note I'm not
necessarily arguing against the creation of a specific unified type --
performance is important for time types).

*) in/out formats...what would be the wire formats of your type -- in
particular, the binary format?

*) do you see your type interacting with various datetime function
(like extract) or will a cast be required? Interval math?

*) how does ordering and uniqueness apply to same timestamps with
unique input time zones?

merlin