如何在准备好的语句中传递带有''（时间戳）的字符串？ [英] How to pass string with ' ' (timestamp) in prepared statement?

问题描述

我正在尝试执行以下查询

I am trying to execute the following query

INSERT INTO hotspot(timestamp) VALUES 
(timestamp with time zone '2012-10-25 14:00:00 +05:00' at time zone 'EET');

我想将时间戳记作为变量传递。

and i want to pass the timestamp as a variable.

我的时间戳列是带有时区的时间戳类型。

My timestamp column is type of timestamp with time zone.

您不知道该怎么做？

当我这样做时（Java，Postgresql）

When i do... (Java, Postgresql)

    String stm= "INSERT INTO hotspot(timestamp) VALUES(timestamp with time zone ? at time zone 'EET')";
    pst = con.prepareStatement(stm);
    pst.setString(1, "2012-08-24 14:00:00 +05:00");
    pst.executeUpdate();

在 $ 1或附近出现语法错误

I get a syntax error at or near "$1"

反正我可以克服这个错误吗？
预先谢谢您！

Is there anyway i can overcome this error?? Thank you in advance!!

更新： 我尝试通过以下方式使用setTimestamp方式...

Update: I tried to use the setTimestamp by the following way...

Calendar c=Calendar.getInstance(TimeZone.getTimeZone("GMT+05:00"));
String stm= "INSERT INTO hotspot(timestamp) VALUES(?)";
pst = con.prepareStatement(stm);
pst.setTimestamp(1,Timestamp.valueOf("2012-01-05 14:00:00"), c );
pst.executeUpdate();

我想数据库中的正确值应该是（关于我的本地时区是EET（ +02））

I suppose that the correct value in the DB should be (regarding that my local time zone is EET (+02))

2012-01-05 11:00:00 +02

2012-01-05 11:00:00 +02

但使用pgadmin i检查值，我得到

but using pgadmin i check the value and i get

2012-01-05 14:00:00 +02

2012-01-05 14:00:00 +02

任何建议？

推荐答案

考虑使用 setTimestamp（）方法而不是 setString（）方法。检查此链接，以了解如何使用 PreparedStatement 。

Consider using the setTimestamp() method instead of setString() method. Check this link in order to understand how to use PreparedStatement.

编辑：如我在评论中所述，请检查用于setTimestamp的API参考（ ），并带有三个参数：

As I explained in the comment, check the API reference for setTimestamp() with three parameters:

使用给定的Calendar对象将指定参数设置为给定的java.sql.Timestamp值
。驱动程序使用Calendar对象
构造一个SQL TIMESTAMP值，然后驱动程序将该值发送到
数据库。使用Calendar对象，驱动程序可以考虑自定义时区来计算
时间戳。如果未指定Calendar对象
，则驱动程序将使用默认时区，即运行应用程序的虚拟机
的时区。

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

这篇关于如何在准备好的语句中传递带有''（时间戳）的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！