pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange behavior of the timezone
4+ messages / 2 participants
[nested] [flat]
* Strange behavior of the timezone
@ 2018-03-27 09:40 Vlad Alexeenkov <vladabc@yandex.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Vlad Alexeenkov @ 2018-03-27 09:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of the timezone
@ 2018-03-27 09:46 Robert Zenz <robert.zenz@sibvisions.com>
parent: Vlad Alexeenkov <vladabc@yandex.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Zenz @ 2018-03-27 09:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of the timezone
@ 2018-03-27 11:04 Vlad Alexeenkov <vladabc@yandex.ru>
parent: Robert Zenz <robert.zenz@sibvisions.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vlad Alexeenkov @ 2018-03-27 11:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of the timezone
@ 2018-03-27 11:15 Robert Zenz <robert.zenz@sibvisions.com>
parent: Vlad Alexeenkov <vladabc@yandex.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Robert Zenz @ 2018-03-27 11:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>>>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-03-27 11:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-27 09:40 Strange behavior of the timezone Vlad Alexeenkov <vladabc@yandex.ru>
2018-03-27 09:46 ` Robert Zenz <robert.zenz@sibvisions.com>
2018-03-27 11:04 ` Vlad Alexeenkov <vladabc@yandex.ru>
2018-03-27 11:15 ` Robert Zenz <robert.zenz@sibvisions.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox