Обсуждение: autovacuum: vacuum analyze vs autovacuum: vacuum

Hi!

Can anyone explain this behavior?

select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at time zone 'Europe/Moscow' as t2

 t1                                       t2
=================== ===================
01.01.1900 15:11:10 01.01.1900 15:40:53
Strange behavior of the timezone




select version()

version                                                                                                 
========================================================================================================
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Thanks

That is not necessarily a strange behavior, as the Timezome most likely includes
all the adjustments that were made in the period of time (leap seconds and the
like). At least as far as I know.

How does it behave if you try a more current date, like today? What is your
system/database timezone?


On 27.03.2018 11:40, Vlad Alexeenkov wrote:
> Hi!
> 
> Can anyone explain this behavior?
> 
> select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at
> time zone 'Europe/Moscow' as t2
> 
>  t1                                       t2
> =================== ===================
> 01.01.1900 15:11:10 01.01.1900 15:40:53
> 
> Strange behavior of the timezone
> 
> 
> 
> 
> select version()
> 
> version
> ========================================================================================================
> 
> PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
> (Red Hat 4.8.5-11), 64-bit
> 
> Thanks
> 
>

On server - UTC
Local - Europe/Moscow

For example, from 1920 all right:

If run in DataGrip:

select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 
15:11:10'::timestamp at time zone 'Europe/Moscow' as t2

1900-01-01 15:11:10.000000    1900-01-01 12:40:53.000000

select '1920-01-01 15:11:10'::timestamp t1, '1920-01-01 
15:11:10'::timestamp at time zone 'Europe/Moscow' as t2

1920-01-01 15:11:10.000000    1920-01-01 12:11:10.000000

select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 
15:11:10'::timestamp at time zone 'UTC-3' as t2

1900-01-01 15:11:10.000000    1900-01-01 12:11:10.000000

Thanks

On 27.03.2018 12:46, Robert Zenz wrote:
> That is not necessarily a strange behavior, as the Timezome most likely includes
> all the adjustments that were made in the period of time (leap seconds and the
> like). At least as far as I know.
>
> How does it behave if you try a more current date, like today? What is your
> system/database timezone?
>
>
> On 27.03.2018 11:40, Vlad Alexeenkov wrote:
>> Hi!
>>
>> Can anyone explain this behavior?
>>
>> select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at
>> time zone 'Europe/Moscow' as t2
>>
>>   t1                                       t2
>> =================== ===================
>> 01.01.1900 15:11:10 01.01.1900 15:40:53
>>
>> Strange behavior of the timezone
>>
>>
>>
>>
>> select version()
>>
>> version
>> ========================================================================================================
>>
>> PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
>> (Red Hat 4.8.5-11), 64-bit
>>
>> Thanks
>>

Here you go:

    https://www.timeanddate.com/time/change/russia/moscow?year=1919

That was what I was talking about, on 1st July, 1919 the clocks were turned
backwards by 31 minutes and 19 seconds. That accounts for the difference you see
here (plus or minus a leapsecond or two, or so, don't know). The remaining hours
difference are something about DST, but I'm too busy to look that up in detail.

What I wanted to say is that timezones do not only take *current* changes into
account, but also *past* changes. Actually, any known clock change is taken into
account, that includes leap seconds and any other adjustment of the clock (may
it be adding or removing time).


On 27.03.2018 13:04, Vlad Alexeenkov wrote:
> On server - UTC
> Local - Europe/Moscow
> 
> For example, from 1920 all right:
> 
> If run in DataGrip:
> 
> select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at
> time zone 'Europe/Moscow' as t2
> 
> 1900-01-01 15:11:10.000000    1900-01-01 12:40:53.000000
> 
> select '1920-01-01 15:11:10'::timestamp t1, '1920-01-01 15:11:10'::timestamp at
> time zone 'Europe/Moscow' as t2
> 
> 1920-01-01 15:11:10.000000    1920-01-01 12:11:10.000000
> 
> select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at
> time zone 'UTC-3' as t2
> 
> 1900-01-01 15:11:10.000000    1900-01-01 12:11:10.000000
> 
> Thanks
> 
> On 27.03.2018 12:46, Robert Zenz wrote:
>> That is not necessarily a strange behavior, as the Timezome most likely includes
>> all the adjustments that were made in the period of time (leap seconds and the
>> like). At least as far as I know.
>>
>> How does it behave if you try a more current date, like today? What is your
>> system/database timezone?
>>
>>
>> On 27.03.2018 11:40, Vlad Alexeenkov wrote:
>>> Hi!
>>>
>>> Can anyone explain this behavior?
>>>
>>> select '1900-01-01 15:11:10'::timestamp t1, '1900-01-01 15:11:10'::timestamp at
>>> time zone 'Europe/Moscow' as t2
>>>
>>>   t1                                       t2
>>> =================== ===================
>>> 01.01.1900 15:11:10 01.01.1900 15:40:53
>>>
>>> Strange behavior of the timezone
>>>
>>>
>>>
>>>
>>> select version()
>>>
>>> version
>>> ========================================================================================================
>>>
>>>
>>> PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
>>> (Red Hat 4.8.5-11), 64-bit
>>>
>>> Thanks
>>>
> 
>