Search code examples
postgresqlsyntax-errorinsert-updatetimestamp-with-timezone

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.

Do you have any idea how this can be done?

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();

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

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

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();

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

but using pgadmin i check the value and i get

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

Any suggestions?

Solution

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

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

    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.