Обсуждение: what is the origin of postgreSQL time
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
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 >
Вложения
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
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!
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
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?
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; > >
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
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 > >
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
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 > >
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