Re: LOCALTIMESTAMP has wrong time zone

"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