postgres UTC different from perl?

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