BTW in Postgresql 8.0 you can do: ALTER TABLE foo ALTER foo_timestamp TYPE timestamp(0) with timezone; It'll do the truncation for you. Regards, Ben "Michael Glaesemann" <grzm@xxxxxxxxxxxxx> wrote in message news:1D9A9108-FA72-4B9C-B1E0-963D85F79487@xxxxxxxxxxxxxxxx > > On Jun 10, 2005, at 11:37 AM, Michael Glaesemann wrote: > >> A short term solution would be to update the column using something like >> update foo set foo_timestamp = date_trunc(foo_timestamp). > > Sorry. That isn't clear (or correct!) Complete example at the bottom of > the email. > > UPDATE foo > SET foo_timestamp = date_trunc('second',foo_timestamp); > > >> http://www.postgresql.org/docs/7.4/interactive/functions- >> datetime.html#FUNCTIONS-DATETIME-TRUNC > > Sorry for any confusion. > > Michael Glaesemann > grzm myrealbox com > > > test=# create table foo (foo_id serial not null unique, foo_timestamp > timestamptz not null) without oids; > NOTICE: CREATE TABLE will create implicit sequence "foo_foo_id_seq" for > serial column "foo.foo_id" > NOTICE: CREATE TABLE / UNIQUE will create implicit index > "foo_foo_id_key" for table "foo" > CREATE TABLE > test=# insert into foo (foo_timestamp) values (current_timestamp); > INSERT 0 1 > test=# insert into foo (foo_timestamp) values (current_timestamp); > INSERT 0 1 > test=# insert into foo (foo_timestamp) values (current_timestamp); > INSERT 0 1 > test=# insert into foo (foo_timestamp) values (current_timestamp); > INSERT 0 1 > test=# select * from foo; > foo_id | foo_timestamp > --------+------------------------------- > 1 | 2005-06-10 11:55:48.459675+09 > 2 | 2005-06-10 11:55:49.363353+09 > 3 | 2005-06-10 11:55:49.951119+09 > 4 | 2005-06-10 11:55:50.771325+09 > (4 rows) > > test=# update foo set foo_timestamp = date_trunc ('second',foo_timestamp); > UPDATE 4 > test=# select * from foo; > foo_id | foo_timestamp > --------+------------------------ > 1 | 2005-06-10 11:55:48+09 > 2 | 2005-06-10 11:55:49+09 > 3 | 2005-06-10 11:55:49+09 > 4 | 2005-06-10 11:55:50+09 > (4 rows) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
