Re: [GENERAL] Now() function
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 [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Now() function
On Jun 11, 2005, at 5:28 AM, David Siebert wrote: Quick question. can you set timestamptz to no fractional seconds? The docs are very useful for things like this: http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html - Name timestamp [ (p) ] timestamp [ (p) ] [ without time zone ] with time zone Storage Size 8 bytes8 bytes Description both date and time both date and time, with time zone Low Value 4713 BC4713 BC High Value5874897 AD 5874897 AD Resolution1 microsecond / 14 digits 1 microsecond / 14 digits snip / time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6 for the timestamp and interval types. - Please always cc the list so others may be able to help, and please don't top post. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Now() function
Windows XP SP2 Java SDK V1.4.2_08 JDBC 7.4.216.jdbc3 When I use now in an update it is giving me a very odd value in the database. This is what PGAdminIII shows 2005-06-09 13:52:46.259715 I am not expecting the decimal seconds. I am getting an out of range error in java when I read the column. I am porting from a V7.1 server over to 7.4 Yes I will soon move it to 8 but I have a working 7.4 server now. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Now() function
On Jun 10, 2005, at 7:07 AM, David Siebert wrote: When I use now in an update it is giving me a very odd value in the database. This is what PGAdminIII shows 2005-06-09 13:52:46.259715 I am not expecting the decimal seconds. I am getting an out of range error in java when I read the column. If you don't want fractional seconds ever, you can change the column datatype to timestamp(0), which will give you a precision of 0 (no fractional seconds). Changing a column datatype pre-v8.0 involves either (a) adding a new column with the datatype you want, updating the new column to have the data you want, and dropping the old column; or (b) hacking the PostgreSQL system catalog. A short term solution would be to update the column using something like update foo set foo_timestamp = date_trunc(foo_timestamp). http://www.postgresql.org/docs/7.4/interactive/functions- datetime.html#FUNCTIONS-DATETIME-TRUNC You can use date_trunc(current_timestamp) in place of now() to make sure that future inserts and updates also truncate fractional seconds if you don't change the column datatype. (current_timestamp is the SQL-spec-compliant spelling of now() ) As a side note, it appears you're using timestamp rather than timestamptz. To be on the safe size, you may want to consider using timestamptz, which records time zone information as well. Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Now() function
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 [EMAIL PROTECTED]
