Re: storing TZ along timestamps

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