Thread: LOCALTIMESTAMP has wrong time zone
Greetings
I'm in Florida (Eastern Time Zone / New York), using Ubuntu 10.04LTS /
Postgresql 8.4.7.
My default timestamps in my database all are +5 hours in the future
(probably GMT), unless I code the 'EST' time zone into the field's default
(timezone('EST'::text, now())).If I use now() it is +5 hours, and if I use
LOCALTIMESTAMP my SQL editor (EMS Postgresql Mgr) changes it to:
'now'::text::timestamp without time zone, which is also +5 hours.
Anywhere I use LOCALTIMESTAMP in my functions, it is shifted +5 hours in the
future. I must use timezone('EST'::text, now()) everywhere, rather than
CURRENT_TIMESTAMP or LOCALTIMESTAMP. Major pain!!
When I type date in Ubuntu Command line, I see correctly: Thu Feb 24
16:13:13 EST 2011
When I run dpkg-reconfigure tzdata, the linux time zone is correct: America
/ New York.
I've set postgresql.conf --> time zone = 'America/New York'
I've used:
psql beta_main -c "SET TIME ZONE EST;"
psql beta_main -c "SHOW TIME ZONE;"
I've even reinstalled the Database from another server (where this is NOT
happening!) after setting above stuff, and restarted postgresql, same
results.
"Jonathan Brinkman" <JB@BlackSkyTech.com> wrote: > My default timestamps in my database all are +5 hours in the future Can you copy paste actual statements and results? -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "Jonathan Brinkman" <JB@BlackSkyTech.com> wrote:
>> My default timestamps in my database all are +5 hours in the future
> Can you copy paste actual statements and results?
In particular, let's see the result of these commands in psql:
show timezone;
select now();
select localtimestamp;
What it sounds like to me is an incorrect default setting of "timezone"
but it's hard to be sure with no concrete data.
regards, tom lane
"Jonathan Brinkman" <JB@BlackSkyTech.com> wrote:
> "createddatetime" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(),
What happens if you use TIMESTAMP WITH TIME ZONE?
Can you provide a more complete, self-contained example, like this?:
test=# drop table if exists show_ts_issue;
DROP TABLE
test=# CREATE TABLE "show_ts_issue" (
test(# "commentid" INTEGER NOT NULL,
test(# "commentdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT
test(# timezone('EST'::text, now()) NOT NULL,
test(# "createddatetime" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT
now(),
test(# "createddatetime2" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT
test(# 'now'::text::timestamp without time zone,
test(# CONSTRAINT "pk_show_ts_issue_commentid" PRIMARY
KEY("commentid")
test(# ) WITH OIDS;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"pk_show_ts_issue_commentid" for table "show_ts_issue"
CREATE TABLE
test=# insert into show_ts_issue (commentid) values (1);
INSERT 16444 1
test=# \x on
Expanded display is on.
test=# select * from show_ts_issue;
-[ RECORD 1 ]----+---------------------------
commentid | 1
commentdatetime | 2011-02-24 17:57:13.788309
createddatetime | 2011-02-24 16:57:14
createddatetime2 | 2011-02-24 16:57:14
-Kevin
"Jonathan Brinkman" <JB@BlackSkyTech.com> writes:
> postgres@Cloud-DB1:~$ psql beta_cms_main -c "show timezone;"
> TimeZone
> ----------
> EST
> (1 row)
Hmmm ... you do realize that that setting will result in EST (GMT-5)
all year round? It's more likely that you want America/New_York.
That's not your immediate problem, though it might be a problem come
daylight savings time.
> postgres@Cloud-DB1:~$ psql beta_cms_main -c "select now();"
> now
> -------------------------------
> 2011-02-24 17:42:40.023498-05
> (1 row)
> postgres@Cloud-DB1:~$ psql beta_cms_main -c "select localtimestamp;"
> timestamp
> ----------------------------
> 2011-02-24 17:42:40.078052
> (1 row)
> All looks correct to me!
Well, that shows that you're getting the correct answer for
localtimestamp in psql, so if your application is getting different
answers, then you need to look into what it is that your application
is doing differently. Possibly you have some other value of timezone in
force in the application's sessions, or there's some conversion going
on in the client that you haven't told us about.
regards, tom lane
postgres@Cloud-DB1:~$ psql beta_cms_main -c "show timezone;"
TimeZone
----------
EST
(1 row)
postgres@Cloud-DB1:~$ psql beta_cms_main -c "select now();"
now
-------------------------------
2011-02-24 17:42:40.023498-05
(1 row)
postgres@Cloud-DB1:~$ psql beta_cms_main -c "select localtimestamp;"
timestamp
----------------------------
2011-02-24 17:42:40.078052
(1 row)
All looks correct to me!
(it is 5:42PM EST now).
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 24, 2011 5:35 PM
To: Kevin Grittner
Cc: JB@BlackSkyTech.com; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] LOCALTIMESTAMP has wrong time zone
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "Jonathan Brinkman" <JB@BlackSkyTech.com> wrote:
>> My default timestamps in my database all are +5 hours in the future
> Can you copy paste actual statements and results?
In particular, let's see the result of these commands in psql:
show timezone;
select now();
select localtimestamp;
What it sounds like to me is an incorrect default setting of "timezone"
but it's hard to be sure with no concrete data.
regards, tom lane
Well, here is the CREATE TABLE if that's what you mean:
CREATE TABLE "clientdata"."general_comments" (
"commentid" INTEGER DEFAULT nextval('general_comments_seq'::regclass) NOT
NULL,
"commentvalue" VARCHAR(255) NOT NULL,
"personid" INTEGER,
"userid" INTEGER NOT NULL,
"commentdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT
timezone('EST'::text, now()) NOT NULL,
"createddatetime" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(),
"createddatetime2" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT
'now'::text::timestamp without time zone,
CONSTRAINT "pk_general_comments_commentid" PRIMARY KEY("commentid"),
) WITH OIDS;
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Thursday, February 24, 2011 5:06 PM
To: JB@BlackSkyTech.com; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] LOCALTIMESTAMP has wrong time zone
"Jonathan Brinkman" <JB@BlackSkyTech.com> wrote:
> My default timestamps in my database all are +5 hours in the future
Can you copy paste actual statements and results?
-Kevin
"Jonathan Brinkman" <JB@BlackSkyTech.com> wrote: > ## I COULDN'T MAKE IT BREAK USING PSQL. That's pretty solid evidence that the problem isn't in the PostgreSQL server. > This didn't always happen, it just started happening on various of > my tables a maybe couple weeks or so ago. I think it is related to > an update, either to Ubuntu 10.04 or Postgresql 8.4. I usually > apt-get update/upgrade whenever I see that updates are available. I would look at /var/log/dpkg.log to see what you installed at the point when things broke. > Also, this only occurs on my production server (Rackspace cloud). > My dev postgres server doesn't do this timestamp time-zone problem > at all. I would be taking a close look at what the differences are. Anything that is the same on both servers can't be the problem, right? I'm going to harp on one other point -- you will almost certainly be better off if you make these columns TIMESTAMP WITH TIME ZONE. This is the type which is meant to represent moments in the stream of time. It will behave as you probably expect in many more circumstances, especially when recording when events occurred. WITHOUT TIME ZONE is mostly useful for scheduling future events which you want to happen at different points in time in different time zones, or for scheduling things which should occur in whatever time is in effect locally when the related date arrives. -Kevin
Solved. Changing the field datatype from TIMESTAMP to TIMESTAMPTZ fixed it. Now I can use now() as the default value. Strange that it just cropped up recently, but you're right we should be including time zone with that timestamp anyways. My deep gratitude for your time and help! JB -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Friday, February 25, 2011 11:15 AM To: JB@BlackSkyTech.com; pgsql-bugs@postgresql.org; 'Tom Lane' Subject: RE: [BUGS] LOCALTIMESTAMP has wrong time zone "Jonathan Brinkman" <JB@BlackSkyTech.com> wrote: > ## I COULDN'T MAKE IT BREAK USING PSQL. That's pretty solid evidence that the problem isn't in the PostgreSQL server. > This didn't always happen, it just started happening on various of > my tables a maybe couple weeks or so ago. I think it is related to > an update, either to Ubuntu 10.04 or Postgresql 8.4. I usually > apt-get update/upgrade whenever I see that updates are available. I would look at /var/log/dpkg.log to see what you installed at the point when things broke. > Also, this only occurs on my production server (Rackspace cloud). > My dev postgres server doesn't do this timestamp time-zone problem > at all. I would be taking a close look at what the differences are. Anything that is the same on both servers can't be the problem, right? I'm going to harp on one other point -- you will almost certainly be better off if you make these columns TIMESTAMP WITH TIME ZONE. This is the type which is meant to represent moments in the stream of time. It will behave as you probably expect in many more circumstances, especially when recording when events occurred. WITHOUT TIME ZONE is mostly useful for scheduling future events which you want to happen at different points in time in different time zones, or for scheduling things which should occur in whatever time is in effect locally when the related date arrives. -Kevin