如何使用准备好的语句在 postgresql 中插入带时区的时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13676168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i insert timestamp with timezone in postgresql with prepared statement?
提问by Mike Vasi
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.
我正在尝试使用准备好的语句将包含日期、时间和时区的字符串插入到我的数据库的时区字段的时间戳中。
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.
问题是 Timestamp.valueof 函数没有考虑字符串包含的时区，因此会导致错误。接受的格式是 yyyy-[m]m-[d]d hh:mm:ss[.f...] 没有提到时区。
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!
有什么办法可以克服吗？？提前致谢！
回答by a_horse_with_no_name
The basic problem is that a java.sql.Timestamp does not contain timezone information. I think it is always assumed to be "local timezone".
基本问题是 java.sql.Timestamp 不包含时区信息。我认为它总是被假定为“本地时区”。
On solution I can think of is to not use a parameter in a PreparedStatement, but a timezone literal in SQL:
我能想到的解决方案是不在 PreparedStatement 中使用参数，而是在 SQL 中使用时区文字：
update foo
set ts_col = timestamp with time zone '2012-08-24 14:00:00 +02:00'`;
Another possible solution could be to pass a properly formatted String to a PrepareStatement that uses to_timestamp():
另一种可能的解决方案是将格式正确的字符串传递给使用 to_timestamp() 的 PrepareStatement：
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");
回答by mariosk89
I believe that you could use one more field in your database, which would include the time zone. And calculate the time manually after you get these two fields
我相信您可以在数据库中再使用一个字段，其中包括时区。并在得到这两个字段后手动计算时间
