Geek Answers Handbook

How to pass a line with `` (timestamp) in a prepared message?

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 a timestamp type with a time zone.

Do you know how to do this?

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 around $ 1 "

Is there any way to overcome this error? Thanks in advance!

Update: I tried using setTimestamp as follows ...

 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 believe that the correct value in the DB should be (relative to the fact 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?

+4
source share
2 answers

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

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

Sets the assigned parameter to the given value java.sql.Timestamp using this Calendar object. The driver uses the Calendar object to create the SQL TIMESTAMP value, which then sends the database driver. Using the Calendar object, the driver can calculate based on the user’s time zone. If the object is a Calendar, the driver uses the default time zone that is used for the virtual machine on which the application is running.

+6
source

Federico Cristina is absolutely right that setTimestamp is the right way to do this.

The reason for the syntax error is that when passing a parameter, you cannot specify a type with a leading type specifier. The INTEGER '4' style is valid only for literals, and not for parameters.

Your code will be PREPARE d and then EXECUTE d at the protocol level. Here's what happens if I PREPARE it:

 regress=> PREPARE blah(timestamptz) AS INSERT INTO hotspot(timestamp) VALUES(timestamp with time zone $1 at time zone 'EET'); ERROR: syntax error at or near "$1" LINE 1: ...otspot(timestamp) VALUES(timestamp with time zone $1 at time...

Since the data type is specified in the query parameter, you can omit it, instead:

 String stm= "INSERT INTO hotspot(\"timestamp\") VALUES(? at time zone 'EET')";

Please note that I also specified "timestamp" twice because it is a reserved word. It will work without quotes in some contexts, but not in others. Choosing a data type name or a reserved word as the column name is usually a bad idea.

+4
source

More articles:


All Articles