Обсуждение: The jdbc and current_timestamp

---

独家！网易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

在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