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)
