Обсуждение: The jdbc and current_timestamp
Hi, all
I get a problem with the jdbc and timestamp data column.
My postgresql running on the Solaris 10 and version is 8.1.6.
The table looks like this,
create DOMAIN CURRENTTIME AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
create table RY_ArticleCollection (
ArtID INT4 not null,
UID INT4 not null,
CollectTime CURRENTTIME not null,
Note VARCHAR(100) null,
constraint PK_RY_ARTICLECOLLECTION primary key (ArtID, UID)
);
ArtID INT4 not null,
UID INT4 not null,
CollectTime CURRENTTIME not null,
Note VARCHAR(100) null,
constraint PK_RY_ARTICLECOLLECTION primary key (ArtID, UID)
);
I can use the psql tool to issue a SQL command "insert" like this:
insert into ry_articlecollection(..., CollectTime) values(..., CURRENT_TIMESTAMP);
The CollectTime always looks correct in the local machine time. But, I test my web application on my PC running the java web server and use the connection pool supplied by the appserver. When I execute the same SQL command to insert data, I get some weird time values and randomly slower than the postgresql server machine.
For example, the machine time is 11:46:xx, and I execute the SQL insert through the jdbc, the record time will be changed to 10:52:xx. I mean the time value is always slower than the machine time but not for a fixed time period.
My java appserver is Sun Java appserver platform 8.2. The jdbc driver is postgresql-8.1-408.jdbc3.jar.
I guess the problem may be the db connection or the connection pool. But not sure, and I tested other web java application on the same machine and same software but not DOMAIN data type on column, that has no such error. So, I modify the column to native data type: 'timestamp', but the error is still there.
Anyone can help me? Thanks.
Steve Yao
独家!网易3G免费邮,还赠送280兆网盘 www.126.com
stevegy@126.com wrote: > For example, the machine time is 11:46:xx, and I execute the SQL insert through the jdbc, the record time will be changedto 10:52:xx. I mean the time value is always slower than the machine time but not for a fixed time period. > > My java appserver is Sun Java appserver platform 8.2. The jdbc driver is postgresql-8.1-408.jdbc3.jar. > > I guess the problem may be the db connection or the connection pool. But not sure, and I tested other web java applicationon the same machine and same software but not DOMAIN data type on column, that has no such error. So, I modifythe column to native data type: 'timestamp', but the error is still there. > > Anyone can help me? Thanks. This one catches a lot of people first time they see it. The time is fixed at the start of the transaction. This lets you do several inserts having the same timestamp. See here for details of timeofday() etc. http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: > The time is fixed at the start of the transaction. This lets you do > several inserts having the same timestamp. I think there's another problem here, which is that he's declared "currenttime" as a timestamp without time zone, but the CURRENT_TIMESTAMP function yields timestamp with time zone, meaning there's a TimeZone-dependent conversion going on. It sounds to me like there's a difference between the TimeZone setting between his web app and his psql, leading to an hour's offset, plus a smaller offset having to do with time-since-transaction-start. > See here for details of timeofday() etc. > http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT And read the preceding chapter's discussion of the different datetime data types. regards, tom lane
Hi,
Thanks for the reply.
I still work on it, but i guess this problem may on my application side not the db server. I mean the appserver connection pool or jdbc configuration. I still can not nail it. Because I have another web application with the same iBATIS jdbc configuration to the appserver connection pool and connect to the same postgres 8.1.6 server, this application runs ok with the current_timestamp.
At this time, I found the time insert into this kind of column is slower than the real time and it is closer to the web appserver start time. The start time is also the connection pool start time. If I restart the appserver, the next row that i insert into the time will update to about the appserver start time. It is not looks like a time zone problem.
Thanks.
regards, Steve Yao
-----原始邮件-----
发件人:"Tom Lane" <tgl@sss.pgh.pa.us>
发送时间:2007-01-17 22:38:51
收件人:"Richard Huxton" <dev@archonet.com>
抄送:stevegy@126.com,pgsql-general@postgresql.org
主题:Re: [GENERAL] The jdbc and current_timestamp
Richard Huxton <dev@archonet.com> writes:
> The time is fixed at the start of the transaction. This lets you do
> several inserts having the same timestamp.
I think there's another problem here, which is that he's declared
"currenttime" as a timestamp without time zone, but the
CURRENT_TIMESTAMP function yields timestamp with time zone,
meaning there's a TimeZone-dependent conversion going on. It sounds
to me like there's a difference between the TimeZone setting between
his web app and his psql, leading to an hour's offset, plus a smaller
offset having to do with time-since-transaction-start.
> See here for details of timeofday() etc.
> http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
And read the preceding chapter's discussion of the different datetime
data types.
regards, tom lane
独家!网易3G免费邮,还赠送280兆网盘 www.126.com
Thanks for the reply.
I still work on it, but i guess this problem may on my application side not the db server. I mean the appserver connection pool or jdbc configuration. I still can not nail it. Because I have another web application with the same iBATIS jdbc configuration to the appserver connection pool and connect to the same postgres 8.1.6 server, this application runs ok with the current_timestamp.
At this time, I found the time insert into this kind of column is slower than the real time and it is closer to the web appserver start time. The start time is also the connection pool start time. If I restart the appserver, the next row that i insert into the time will update to about the appserver start time. It is not looks like a time zone problem.
Thanks.
regards, Steve Yao
-----原始邮件-----
发件人:"Tom Lane" <tgl@sss.pgh.pa.us>
发送时间:2007-01-17 22:38:51
收件人:"Richard Huxton" <dev@archonet.com>
抄送:stevegy@126.com,pgsql-general@postgresql.org
主题:Re: [GENERAL] The jdbc and current_timestamp
Richard Huxton <dev@archonet.com> writes:
> The time is fixed at the start of the transaction. This lets you do
> several inserts having the same timestamp.
I think there's another problem here, which is that he's declared
"currenttime" as a timestamp without time zone, but the
CURRENT_TIMESTAMP function yields timestamp with time zone,
meaning there's a TimeZone-dependent conversion going on. It sounds
to me like there's a difference between the TimeZone setting between
his web app and his psql, leading to an hour's offset, plus a smaller
offset having to do with time-since-transaction-start.
> See here for details of timeofday() etc.
> http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
And read the preceding chapter's discussion of the different datetime
data types.
regards, tom lane
独家!网易3G免费邮,还赠送280兆网盘 www.126.com
Hi,
I found the point. The auto_commit flag and jdbc connection pool is some of problem and the current_timestamp is not a now() meanful function. I have to use the CAST(timeofday() as timestamp) to.
I refer to the http://archives.postgresql.org/pgsql-jdbc/2006-08/msg00063.php.
Thanks.
Regards, Steve Yao
在2007-01-17,"Tom Lane" 写道:
Richard Huxton <dev@archonet.com> writes: > The time is fixed at the start of the transaction. This lets you do > several inserts having the same timestamp. I think there's another problem here, which is that he's declared "currenttime" as a timestamp without time zone, but the CURRENT_TIMESTAMP function yields timestamp with time zone, meaning there's a TimeZone-dependent conversion going on. It sounds to me like there's a difference between the TimeZone setting between his web app and his psql, leading to an hour's offset, plus a smaller offset having to do with time-since-transaction-start. > See here for details of timeofday() etc. > http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT And read the preceding chapter's discussion of the different datetime data types. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
独家!网易3G免费邮,还赠送280兆网盘 www.126.com