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-0509: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-0909: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-1309: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-1809: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-2209: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-2709: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-3109: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-0609: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-1009: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-1509: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:002011-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:002011-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:002011-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:002011-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:002011-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:002012-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:002012-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:002012-05-01 17:00:00 2012-05-08 17:00:00
 
...

Cheers,
Steve