Обсуждение: BUG #6340: to_timestamp() miscalculates

Поиск
Список
Период
Сортировка

BUG #6340: to_timestamp() miscalculates

От
hfuxelius@gmail.com
Дата:
The following bug has been logged on the website:

Bug reference:      6340
Logged by:          Hans-Henrik Fuxelius
Email address:      hfuxelius@gmail.com
PostgreSQL version: 9.1.1
Operating system:   Ubuntu 11.10
Description:=20=20=20=20=20=20=20=20

test to run: select to_timestamp('2011,03,27,02,11,11',
'YYYY,MM,DD,HH24,MI,SS')

and the run: select to_timestamp('2011,03,27,03,11,11',
'YYYY,MM,DD,HH24,MI,SS')

It miscalculates 02 hour to 03 hour by some reason

Re: BUG #6340: to_timestamp() miscalculates

От
Tom Lane
Дата:
hfuxelius@gmail.com writes:
> test to run: select to_timestamp('2011,03,27,02,11,11',
> 'YYYY,MM,DD,HH24,MI,SS')

> and the run: select to_timestamp('2011,03,27,03,11,11',
> 'YYYY,MM,DD,HH24,MI,SS')

> It miscalculates 02 hour to 03 hour by some reason

If that date is a DST forward transition date in your timezone, then
this behavior is neither surprising nor a bug.

            regards, tom lane

Re: BUG #6340: to_timestamp() miscalculates

От
"Kevin Grittner"
Дата:
wrote:

> test to run: select to_timestamp('2011,03,27,02,11,11',
> 'YYYY,MM,DD,HH24,MI,SS')
>
> and the run: select to_timestamp('2011,03,27,03,11,11',
> 'YYYY,MM,DD,HH24,MI,SS')
>
> It miscalculates 02 hour to 03 hour by some reason

I don't know what time zone you're in, or whether daylight saving
time kicks in on that date.  *Is* there a 02:11:11 on that date, or
is that when time "springs forward" by an hour in the spring for your
locale?

-Kevin

Re: BUG #6340: to_timestamp() miscalculates

От
Hans-Henrik Fuxelius
Дата:
Thanx a lot for your promt respons :) You are absolutely right, its the day=
light saving that date and hour, I had it as an key in my GPS data and was =
very confused!!!

Again - Thanx
/Hans-Henrik
On Dec 15, 2011, at 5:20 PM, Kevin Grittner wrote:

> wrote:
>=20
>> test to run: select to_timestamp('2011,03,27,02,11,11',
>> 'YYYY,MM,DD,HH24,MI,SS')
>>=20
>> and the run: select to_timestamp('2011,03,27,03,11,11',
>> 'YYYY,MM,DD,HH24,MI,SS')
>>=20
>> It miscalculates 02 hour to 03 hour by some reason
>=20
> I don't know what time zone you're in, or whether daylight saving
> time kicks in on that date.  *Is* there a 02:11:11 on that date, or
> is that when time "springs forward" by an hour in the spring for your
> locale?
>=20
> -Kevin

Re: BUG #6340: to_timestamp() miscalculates

От
Hans-Henrik Fuxelius
Дата:
It seems not possible to suppress to_timestamp from doing daylight
saving conversion, I have GPS timeseries that looks perfect in the
'timestamp without time zone' save one value that wreck the whole time
serie, does that mean that time series cant be stored in time stamps?

/Hans-Henrik

On Thu, Dec 15, 2011 at 5:56 PM, Hans-Henrik Fuxelius
<hfuxelius@gmail.com> wrote:
> Thanx a lot for your promt respons :) You are absolutely right, its the d=
aylight saving that date and hour, I had it as an key in my GPS data and wa=
s very confused!!!
>
> Again - Thanx
> /Hans-Henrik
> On Dec 15, 2011, at 5:20 PM, Kevin Grittner wrote:
>
>> wrote:
>>
>>> test to run: select to_timestamp('2011,03,27,02,11,11',
>>> 'YYYY,MM,DD,HH24,MI,SS')
>>>
>>> and the run: select to_timestamp('2011,03,27,03,11,11',
>>> 'YYYY,MM,DD,HH24,MI,SS')
>>>
>>> It miscalculates 02 hour to 03 hour by some reason
>>
>> I don't know what time zone you're in, or whether daylight saving
>> time kicks in on that date. =A0*Is* there a 02:11:11 on that date, or
>> is that when time "springs forward" by an hour in the spring for your
>> locale?
>>
>> -Kevin
>

Re: BUG #6340: to_timestamp() miscalculates

От
Robert Haas
Дата:
On Fri, Dec 16, 2011 at 3:43 AM, Hans-Henrik Fuxelius
<hfuxelius@gmail.com> wrote:
> It seems not possible to suppress to_timestamp from doing daylight
> saving conversion, I have GPS timeseries that looks perfect in the
> 'timestamp without time zone' save one value that wreck the whole time
> serie, does that mean that time series cant be stored in time stamps?

I think it mostly means that you shouldn't use timestamp without time zone.

Or else you should use a time zone setting that doesn't involve DST.

Throwing away the time zone information is bad, because YYYY-MM-DD
HH:MM:SS doesn't uniquely identify a point in time, even if you know
what time zone it's relative to.  That combination of years, minutes,
days, hours, minutes, and seconds can occur twice, if it's near a DST
boundary.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company