Re: 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