Hi,
I update a row's TIMESTAMP WITH TIME ZONE column value with:
... SET <colname> = 'now'::TIMESTAMP WITH TIME ZONE ...
Initially, I was hesitating with CURRENT_TIMESTAMP, but in a
table description, a column with a default value of 'now' or
CURRENT_TIMESTAMP will both be printed "not null default
('now'::text)::timestamp(6) with time zone" so I assumed the
correct way of writing it was 'now' and then to cast to a
timestamp with/without time zone.
However, I have seen that some entries in the table get wrongly
updated to the same timestamp in the past. Just like if the
updates after one of them were then using always the same
timestamp as this one instead of 'now'. I use prepared
statements, so I suppose there might be something wrong there
with optimization or the fact that the query is sent to the
server. I use prepared statements with
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY and
a prepare threshold of 5.
I have experimented with CURRENT_TIMESTAMP instead of 'now', and
it "fixes" the problem. Also, and now it's getting kinda funny,
using ('now'::text)::TIMESTAMP WITH TIME ZONE also "fixes" the
problem. So only the 'now'::TIMESTAMP WITH TIME ZONE form
exhausts the problem. So I was curious about it, a feature, a
bug, a misunderstanding from my side?
In the documentation, I could find that "String literals
specifying time-varying date/time values, such as 'now' or
'today' will no longer work as expected in column default
expressions" in the 7.4 release notes, but that's only talking
about column default expressions right? [1] seems to confirm (in
the form "TIMESTAMP 'now'") that the problem is limited to column
default values.
I am unsure if "not null default ('now'::text)::timestamp(6) with
time zone" in the table description is not a bit misleading then,
since CURRENT_TIMESTAMP or now() should be rather used - or maybe
'now' should be deprecated completely?
Using postgresql-8.1-407.jdbc3.jar over java 1.4.2 connecting to
a 7.4 database.
Ref:
[1] http://www.postgresql.org/docs/{7.4,8.1}/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
