Postgres Pro
Downloads
Home   >   mailing lists

Thread: postgres UTC different from perl?

postgres UTC different from perl?

From
Louis-David Mitterrand
Date:
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?

Re: postgres UTC different from perl?

From
Richard Huxton
Date:
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

Re: postgres UTC different from perl?

From
Tom Lane
Date:
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

Re: postgres UTC different from perl?

From
Louis-David Mitterrand
Date:
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?

Re: postgres UTC different from perl?

From
Tom Lane
Date:
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