Обсуждение: Now() function

Поиск
Список
Период
Сортировка

Now() function

От
David Siebert
Дата:
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.

Re: Now() function

От
Michael Glaesemann
Дата:
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

Re: Now() function

От
Michael Glaesemann
Дата:
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)

Re: Now() function

От
Michael Glaesemann
Дата:
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 bytes                    8 bytes
Description   both date and time         both date and time, with
time zone
Low Value     4713 BC                    4713 BC
High Value    5874897 AD                 5874897 AD
Resolution    1 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

Re: Now() function

От
"Ben Trewern"
Дата:
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
>