pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat is the origin of postgreSQL time
12+ messages / 5 participants
[nested] [flat]
* what is the origin of postgreSQL time
@ 2008-03-31 09:05 Julius Tuskenis <julius@nsoft.lt>
0 siblings, 2 replies; 12+ messages in thread
From: Julius Tuskenis @ 2008-03-31 09:05 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 09:20 Thomas Markus <t.markus@proventis.net>
parent: Julius Tuskenis <julius@nsoft.lt>
1 sibling, 1 reply; 12+ messages in thread
From: Thomas Markus @ 2008-03-31 09:20 UTC (permalink / raw)
To: pgsql-admin
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
>
Attachments:
t_markus.vcf (text/x-vcard, 267B) download
begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 10:18 Julius Tuskenis <julius@nsoft.lt>
parent: Thomas Markus <t.markus@proventis.net>
0 siblings, 1 reply; 12+ messages in thread
From: Julius Tuskenis @ 2008-03-31 10:18 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 12:04 Vishal Arora <aroravishal22@hotmail.com>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 1 reply; 12+ messages in thread
From: Vishal Arora @ 2008-03-31 12:04 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
_________________________________________________________________
Education: Are exams worrying you all the day long? Write to MSN education experts for help.
http://education.in.msn.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 12:09 Julius Tuskenis <julius@nsoft.lt>
parent: Vishal Arora <aroravishal22@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Julius Tuskenis @ 2008-03-31 12:09 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 12:17 Tommy Gildseth <tommy.gildseth@usit.uio.no>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 1 reply; 12+ messages in thread
From: Tommy Gildseth @ 2008-03-31 12:17 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 12:24 Julius Tuskenis <julius@nsoft.lt>
parent: Tommy Gildseth <tommy.gildseth@usit.uio.no>
0 siblings, 0 replies; 12+ messages in thread
From: Julius Tuskenis @ 2008-03-31 12:24 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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;
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Julius Tuskenis <julius@nsoft.lt>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2008-03-31 14:23 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 15:39 Julius Tuskenis <julius@nsoft.lt>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Julius Tuskenis @ 2008-03-31 15:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-03-31 15:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2008-03-31 15:47 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-04-01 09:40 Julius Tuskenis <julius@nsoft.lt>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Julius Tuskenis @ 2008-04-01 09:40 UTC (permalink / raw)
To: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: what is the origin of postgreSQL time
@ 2008-04-01 14:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2008-04-01 14:32 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-04-01 14:32 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-31 09:05 what is the origin of postgreSQL time Julius Tuskenis <julius@nsoft.lt>
2008-03-31 09:20 ` Thomas Markus <t.markus@proventis.net>
2008-03-31 10:18 ` Julius Tuskenis <julius@nsoft.lt>
2008-03-31 12:04 ` Vishal Arora <aroravishal22@hotmail.com>
2008-03-31 12:09 ` Julius Tuskenis <julius@nsoft.lt>
2008-03-31 12:17 ` Tommy Gildseth <tommy.gildseth@usit.uio.no>
2008-03-31 12:24 ` Julius Tuskenis <julius@nsoft.lt>
2008-03-31 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-31 15:39 ` Julius Tuskenis <julius@nsoft.lt>
2008-03-31 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-01 09:40 ` Julius Tuskenis <julius@nsoft.lt>
2008-04-01 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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