Обсуждение: postgres UTC different from perl?
Hi, when trying: psql template1 -c "select date_part('epoch',current_date at time zone 'UTC');" date_part ------------ 1198015200 the result is different from perl -MDateTime -le 'print DateTime->today(time_zone => "UTC")->epoch;' 1198022400 Is there an issue with postgresql?
Louis-David Mitterrand wrote: > Hi, > > when trying: > > psql template1 -c "select date_part('epoch',current_date at time zone 'UTC');" > date_part > ------------ > 1198015200 > > the result is different from > > perl -MDateTime -le 'print DateTime->today(time_zone => "UTC")->epoch;' > 1198022400 > > Is there an issue with postgresql? Well, there's one of 3 possibilities: 1. PostgreSQL's date/time code is broken. 2. Perl's DateTime module is broken. 3. One of those bits of code doesn't do what you think it does. I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think it does. Try setting your timezone to various offsets and exploring. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: > I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think > it does. Try setting your timezone to various offsets and exploring. In fact, I think it's adjusting in exactly the wrong direction. I get the right number from regression=# select date_part('epoch', 'today'::timestamp at time zone 'UTC'); date_part ------------ 1198022400 (1 row) and the wrong one from regression=# select date_part('epoch', 'today'::timestamptz at time zone 'UTC'); date_part ------------ 1198058400 (1 row) and I think the locution with CURRENT_DATE is equivalent to the second case because timestamptz is the preferred type to promote date to. regards, tom lane
On Wed, Dec 19, 2007 at 08:14:17PM -0500, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think > > it does. Try setting your timezone to various offsets and exploring. > > In fact, I think it's adjusting in exactly the wrong direction. > > I get the right number from > > regression=# select date_part('epoch', 'today'::timestamp at time zone 'UTC'); > date_part > ------------ > 1198022400 > (1 row) > > and the wrong one from > > regression=# select date_part('epoch', 'today'::timestamptz at time zone 'UTC'); > date_part > ------------ > 1198058400 > (1 row) > > and I think the locution with CURRENT_DATE is equivalent to the second > case because timestamptz is the preferred type to promote date to. Does that mean it's a postgresql bug?
Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes: > On Wed, Dec 19, 2007 at 08:14:17PM -0500, Tom Lane wrote: >> Richard Huxton <dev@archonet.com> writes: >>> I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think >>> it does. Try setting your timezone to various offsets and exploring. > Does that mean it's a postgresql bug? No. regards, tom lane