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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
