Thread: inserting a timestamp show wrong timezone

When I create a table & insert records, I am getting the wrong timezone info
(-04)
When I insert using now() -- the timezone is right (-05)
I am on EST.   Please help --

example:

 insert into foo  values (3, '12-aug-2001:00:01:01');
insert into foo values (4,now());
showaltb=> select * from foo;
 n |           t
---+------------------------
 3 | 2001-08-12 00:01:01-04
 4 | 2002-02-20 08:59:56-05

Pam Wampler <Pam_Wampler@taylorwhite.com> writes:
> example:

>  insert into foo  values (3, '12-aug-2001:00:01:01');
> insert into foo values (4,now());
> showaltb=> select * from foo;
>  n |           t
> ---+------------------------
>  3 | 2001-08-12 00:01:01-04
>  4 | 2002-02-20 08:59:56-05

Looks fine to me.  -05 is EST, -04 is EDT ... and I'd expect
daylight-savings time to be used in August ...

If you don't want daylight-savings time to be used, set your
TimeZone variable to something like "EST", not "ESTEDT" (the
exact form of timezone specs tends to vary across Unixen, so
you'll have to experiment or read the manual to find out what
to use).

            regards, tom lane

Tom
Is there a way to set time zone permanently?
I tried it on the template1 database -- then exited & went back in &
did a show time zone  & said was unknown.
I need to know how to permanently set the time zone to EST
when I do a uname -a on Freebsd -- it shows EST -- but inside psql -- it
doesn't
unless I set time zone 'EST' -- and then is for only the duration of the
session.
Would it work if I put it in the postgresql.conf file?
where do you put global settings for the database ?

thanks
Pam

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 20, 2002 10:23 AM
To: Pam Wampler
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] inserting a timestamp show wrong timezone


Pam Wampler <Pam_Wampler@taylorwhite.com> writes:
> example:

>  insert into foo  values (3, '12-aug-2001:00:01:01');
> insert into foo values (4,now());
> showaltb=> select * from foo;
>  n |           t
> ---+------------------------
>  3 | 2001-08-12 00:01:01-04
>  4 | 2002-02-20 08:59:56-05

Looks fine to me.  -05 is EST, -04 is EDT ... and I'd expect
daylight-savings time to be used in August ...

If you don't want daylight-savings time to be used, set your
TimeZone variable to something like "EST", not "ESTEDT" (the
exact form of timezone specs tends to vary across Unixen, so
you'll have to experiment or read the manual to find out what
to use).

            regards, tom lane