Обсуждение: what is the origin of postgreSQL time

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

what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
Hello,

I have problem with postgreSQL current_timestramp. Where does it get its
value, because it doesnt match system time. The problem appeared after
this saturday-sunday night and is connected somehow with the daylight
saving. Now "date" on the server returns good value, but the SELECT
current_timestmap is 1 hour late.

Postgresql 8.1.4
OS: Gentoo Linux

--
Julius Tuskenis



Re: what is the origin of postgreSQL time

От
Thomas Markus
Дата:
beware of timezone dst offset. try
select current_timestamp AT TIME ZONE 'MEST'

thomas

Julius Tuskenis schrieb:
> Hello,
>
> I have problem with postgreSQL current_timestramp. Where does it get
> its value, because it doesnt match system time. The problem appeared
> after this saturday-sunday night and is connected somehow with the
> daylight saving. Now "date" on the server returns good value, but the
> SELECT current_timestmap is 1 hour late.
>
> Postgresql 8.1.4
> OS: Gentoo Linux
>


Вложения

Re: what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
If I use select current_timestamp AT TIME ZONE 'EEST' I get good time
(EEST - I'm in Lithuania). But how do I make postgreSQL use system time
by default? The thing is we have 8 servers working and none of them is
affected by this issue. Only one refuses to switch to summer time.

Julius Tuskenis

Thomas Markus rašė:
> beware of timezone dst offset. try
> select current_timestamp AT TIME ZONE 'MEST'
>
> thomas
>
> Julius Tuskenis schrieb:
>> Hello,
>>
>> I have problem with postgreSQL current_timestramp. Where does it get
>> its value, because it doesnt match system time. The problem appeared
>> after this saturday-sunday night and is connected somehow with the
>> daylight saving. Now "date" on the server returns good value, but the
>> SELECT current_timestmap is 1 hour late.
>>
>> Postgresql 8.1.4
>> OS: Gentoo Linux
>>
>
> ------------------------------------------------------------------------
>
>
>


--
Julius Tuskenis
Programuotojas
UAB nSoft
mob. +37068233050


Re: what is the origin of postgreSQL time

От
Vishal Arora
Дата:
 
Have you checked timezone parameter in postgresql.conf file?
 
- Vishal






> Date: Mon, 31 Mar 2008 13:18:41 +0300
> From: julius@nsoft.lt
> CC: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] what is the origin of postgreSQL time
>
> If I use select current_timestamp AT TIME ZONE 'EEST' I get good time
> (EEST - I'm in Lithuania). But how do I make postgreSQL use system time
> by default? The thing is we have 8 servers working and none of them is
> affected by this issue. Only one refuses to switch to summer time.
>
> Julius Tuskenis
>
> Thomas Markus rašė:
> > beware of timezone dst offset. try
> > select current_timestamp AT TIME ZONE 'MEST'
> >
> > thomas
> >
> > Julius Tuskenis schrieb:
> >> Hello,
> >>
> >> I have problem with postgreSQL current_timestramp. Where does it get
> >> its value, because it doesnt match system time. The problem appeared
> > > after this saturday-sunday night and is connected somehow with the
> >> daylight saving. Now "date" on the server returns good value, but the
> >> SELECT current_timestmap is 1 hour late.
> >>
> >> Postgresql 8.1.4
> >> OS: Gentoo Linux
> >>
> >
> > ------------------------------------------------------------------------
> >
> >
> >
>
>
> --
> Julius Tuskenis
> Programuotojas
> UAB nSoft
> mob. +37068233050
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin



Windows Live Messenger : Get connected, share yourself, make a difference the way you chat. Check it out!

Re: what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
Yes, it is undefined (#timezone = unknown) just like in other servers.

Julius

Vishal Arora rašė:
>
> Have you checked timezone parameter in postgresql.conf file?
>
> - Vishal
>
>
>
>
> ------------------------------------------------------------------------
>
> > Date: Mon, 31 Mar 2008 13:18:41 +0300
> > From: julius@nsoft.lt
> > CC: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] what is the origin of postgreSQL time
> >
> > If I use select current_timestamp AT TIME ZONE 'EEST' I get good time
> > (EEST - I'm in Lithuania). But how do I make postgreSQL use system time
> > by default? The thing is we have 8 servers working and none of them is
> > affected by this issue. Only one refuses to switch to summer time.
> >
> > Julius Tuskenis
> >
> > Thomas Markus rašė:
> > > beware of timezone dst offset. try
> > > select current_timestamp AT TIME ZONE 'MEST'
> > >
> > > thomas
> > >
> > > Julius Tuskenis schrieb:
> > >> Hello,
> > >>
> > >> I have problem with postgreSQL current_timestramp. Where does it get
> > >> its value, because it doesnt match system time. The problem appeared
> > > > after this saturday-sunday night and is connected somehow with the
> > >> daylight saving. Now "date" on the server returns good value, but
> the
> > >> SELECT current_timestmap is 1 hour late.
> > >>
> > >> Postgresql 8.1.4
> > >> OS: Gentoo Linux
> > >>
> > >
> > >
> ------------------------------------------------------------------------
> > >
> > >
> > >
> >
> >
> > --
> > Julius Tuskenis
> > Programuotojas
> > UAB nSoft
> > mob. +37068233050
> >
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
>
>
> ------------------------------------------------------------------------
> Windows Live Messenger : Get connected, share yourself, make a
> difference the way you chat. Check it out!
> <http://get.live.com/messenger/overview%20>


--
Julius Tuskenis
Programuotojas
UAB nSoft
mob. +37068233050


Re: what is the origin of postgreSQL time

От
Tommy Gildseth
Дата:
How about database specific config settings?
SELECT datname, datconfig FROM pg_database;


--
Tommy Gildseth


Julius Tuskenis wrote:
> Yes, it is undefined (#timezone = unknown) just like in other servers.
>
> Julius
>
> Vishal Arora rašė:
>>
>> Have you checked timezone parameter in postgresql.conf file?

Re: what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
Just empty strings in datconfig.

"postgres";""
"sportlog";""
"template1";""
"template0";""
"sport";""


Tommy Gildseth rašė:
> How about database specific config settings?
> SELECT datname, datconfig FROM pg_database;
>
>

Re: what is the origin of postgreSQL time

От
Tom Lane
Дата:
Julius Tuskenis <julius@nsoft.lt> writes:
> I have problem with postgreSQL current_timestramp. Where does it get its
> value, because it doesnt match system time. The problem appeared after
> this saturday-sunday night and is connected somehow with the daylight
> saving. Now "date" on the server returns good value, but the SELECT
> current_timestmap is 1 hour late.

> Postgresql 8.1.4
             ^^^^^

I haven't checked the timezone history, but 8.1.4 was quite a few
timezone updates ago.  Would a DST switch have happened this weekend
under old law where you live?  If so, you need to update to a more
modern set of timezone files.  Look under (probably, I don't know
gentoo) /usr/share/postgresql/timezone/

            regards, tom lane

Re: what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
Thank you for your answer.
I've noticed that statement SELECT current_timestamp AT TIME ZONE
'Europe/Vilnius' returns me an error:
 ERROR: time zone "Europe/Vilnius" not recognized
SQL state: 22023
Time zone Europe/Vilnius is the systems timezone. I checked in
pgsql/share/timezone the file is in there. What could be wrong?

Julius Tuskenis


Tom Lane rašė:
> Julius Tuskenis <julius@nsoft.lt> writes:
>
>> I have problem with postgreSQL current_timestramp. Where does it get its
>> value, because it doesnt match system time. The problem appeared after
>> this saturday-sunday night and is connected somehow with the daylight
>> saving. Now "date" on the server returns good value, but the SELECT
>> current_timestmap is 1 hour late.
>>
>
>
>> Postgresql 8.1.4
>>
>              ^^^^^
>
> I haven't checked the timezone history, but 8.1.4 was quite a few
> timezone updates ago.  Would a DST switch have happened this weekend
> under old law where you live?  If so, you need to update to a more
> modern set of timezone files.  Look under (probably, I don't know
> gentoo) /usr/share/postgresql/timezone/
>
>             regards, tom lane
>
>


Re: what is the origin of postgreSQL time

От
Tom Lane
Дата:
Julius Tuskenis <julius@nsoft.lt> writes:
> I've noticed that statement SELECT current_timestamp AT TIME ZONE
> 'Europe/Vilnius' returns me an error:
>  ERROR: time zone "Europe/Vilnius" not recognized
> SQL state: 22023
> Time zone Europe/Vilnius is the systems timezone. I checked in
> pgsql/share/timezone the file is in there. What could be wrong?

Huh, works for me.  Maybe there is something wrong with the file
permissions on the Europe/Vilnius file or one of the containing
directories?  If the PG server process can't get at that file
you'd get an error like this.

            regards, tom lane

Re: what is the origin of postgreSQL time

От
Julius Tuskenis
Дата:
I gues it would work for you:) It works on 8 servers, still not on this
one. Permissions seem to be set ok too. Where does postgresql store its
path to timezone directory? Is there any way to see where is it looking
for timezone file 'Europe/Vilnius' or 'Egypt' (doesnt work too). In fact
works only abbreviated timezones like MEST, EEST, GMT etc.... This beats
me. I cant even imagine what to look for further.

--
Julius Tuskenis



Tom Lane rašė:
> Julius Tuskenis <julius@nsoft.lt> writes:
>
>> I've noticed that statement SELECT current_timestamp AT TIME ZONE
>> 'Europe/Vilnius' returns me an error:
>>  ERROR: time zone "Europe/Vilnius" not recognized
>> SQL state: 22023
>> Time zone Europe/Vilnius is the systems timezone. I checked in
>> pgsql/share/timezone the file is in there. What could be wrong?
>>
>
>  Maybe there is something wrong with the file
> permissions on the Europe/Vilnius file or one of the containing
> directories?  If the PG server process can't get at that file
> you'd get an error like this.
>
>             regards, tom lane
>
>




Re: what is the origin of postgreSQL time

От
Tom Lane
Дата:
Julius Tuskenis <julius@nsoft.lt> writes:
> I gues it would work for you:) It works on 8 servers, still not on this
> one. Permissions seem to be set ok too. Where does postgresql store its
> path to timezone directory? Is there any way to see where is it looking
> for timezone file 'Europe/Vilnius' or 'Egypt' (doesnt work too). In fact
> works only abbreviated timezones like MEST, EEST, GMT etc.... This beats
> me. I cant even imagine what to look for further.

Too bad this is 8.1; on something newer, the contents of the
pg_timezone_names view would be informative.

I still suspect an access permissions problem, but the only way I can
think of to be sure is to strace the Postgres backend process while
you issue SET TIMEZONE, and see what filename it tries to open and
what the kernel's return code is.

            regards, tom lane