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.

Search Discussions

  • Kevin Grittner at Feb 24, 2011 at 10:06 pm

    "Jonathan Brinkman" wrote:

    My default timestamps in my database all are +5 hours in the future
    Can you copy paste actual statements and results?

    -Kevin
  • Tom Lane at Feb 24, 2011 at 10:35 pm

    "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
    "Jonathan Brinkman" 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 at Feb 24, 2011 at 10:43 pm
    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
    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" 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
  • Tom Lane at Feb 24, 2011 at 11:23 pm

    "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
  • Jonathan Brinkman at Feb 24, 2011 at 10:44 pm
    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
    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" 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 at Feb 24, 2011 at 10:58 pm

    "Jonathan Brinkman" 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
  • Kevin Grittner at Feb 25, 2011 at 4:15 pm

    "Jonathan Brinkman" 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
  • Jonathan Brinkman at Feb 25, 2011 at 4:50 pm
    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
    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" 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedFeb 24, '11 at 9:23p
activeFeb 25, '11 at 4:50p
posts9
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase