如何在带有预准备语句的PostgreSQL中插入带时区的时间戳? [英] How can i insert timestamp with timezone in postgresql with prepared statement?

问题描述

我正在尝试使用准备好的语句将包含日期，时间和时区的字符串插入数据库DB的带有timezone字段的时间戳中.

I am trying to insert to a timestamp with timezone field of my DB a string which includes date, time and timezone using prepared statement.

问题在于Timestamp.valueof函数未考虑字符串包含的时区，因此会导致错误. 可接受的格式为yyyy- [m] m- [d] d hh:mm:ss [.f ...]，其中未提及时区.

The problem is that Timestamp.valueof function does not take into consideration the time zone that the string inludes so it causes an error. The accepted format is yyyy-[m]m-[d]d hh:mm:ss[.f...] which does not mention timezone.

那是导致错误的确切代码:

That is the exact code that causes the error:

pst.setTimestamp(2，Timestamp.valueOf("2012-08-24 14:00:00 +02:00"))

pst.setTimestamp(2,Timestamp.valueOf("2012-08-24 14:00:00 +02:00"))

有什么办法可以克服它吗? 预先感谢！

Is there any way that i can overcome it?? Thanks in advance!

推荐答案

基本问题是java.sql.Timestamp不包含时区信息.我认为总是假定它是本地时区".

The basic problem is that a java.sql.Timestamp does not contain timezone information. I think it is always assumed to be "local timezone".

在解决方案上，我能想到的是不使用PreparedStatement中的参数，而是使用SQL中的时区文字:

On solution I can think of is to not use a parameter in a PreparedStatement, but a timezone literal in SQL:

update foo
  set ts_col = timestamp with time zone '2012-08-24 14:00:00 +02:00'`;

另一种可能的解决方案是将正确格式的字符串传递给使用to_timestamp()的PrepareStatement:

Another possible solution could be to pass a properly formatted String to a PrepareStatement that uses to_timestamp():

String sql = "update foo set ts_col = to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss')"; 
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "2012-08-24 14:00:00 +02:00");

这篇关于如何在带有预准备语句的PostgreSQL中插入带时区的时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！