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@myrealbox.com> wrote in message
news:1D9A9108-FA72-4B9C-B1E0-963D85F79487@myrealbox.com...
>
> 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@postgresql.org
>
