How to pass string with ' ' (timestamp) in prepared statement?

Question

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?

timestamp is a column of hotspot table? Isn't timestamp a reserved word? — Federico Cristina, Dec 03 '12 at 23:48
@FedericoCristina Yes timestamp is a column in table hotspot. I dont think that timestamp is a reserved word — Mike Vasi, Dec 03 '12 at 23:52
Well, timestamp is a data type, and it is *at least* a bad habit (IMnsvHO)to give your column names the names of the datatype. Would you name your child "child" ? — wildplasser, Dec 03 '12 at 23:54
@Michelangelo you are right about timestamp column. http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html — Federico Cristina, Dec 03 '12 at 23:56

Federico Cristina · Accepted Answer · 2012-12-04T00:14:50.880

6

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.

edited Dec 04 '12 at 00:14

answered Dec 03 '12 at 23:49

Federico Cristina

2,203
1
19
37

When i used setTimestamp() i couldnt make the DB set a time zone different than the default one that is why i chose the afore mentioned way – Mike Vasi Dec 03 '12 at 23:58
1

Then consider using the [more complete option](http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setTimestamp%28int,%20java.sql.Timestamp,%20java.util.Calendar%29) of setTimeStamp(), the one with three parameters. With a _Calendar_ object, the driver can calculate the timestamp taking into account a custom timezone. – Federico Cristina Dec 04 '12 at 00:07
1

@MichelangeloVandilakis Rather than messing with `AT TIME ZONE`, consider setting `TimeZone` in the connection correctly for your location, and storing `timestamp with time zone` values instead of the quirky-and-hard-to-get-right `timestamp without time zone` (unqualified `timestamp`). – Craig Ringer Dec 04 '12 at 00:17
@CraigRinger I forgot to mention that timestamp column in my DB is type of timestamp with time zone! – Mike Vasi Dec 04 '12 at 00:24
@FedericoCristina According to my update (if it is correct ) it doesnt work like mentioned above – Mike Vasi Dec 04 '12 at 22:40

score 4 · Answer 2 · answered Dec 04 '12 at 00:13

4

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

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

Your code will be PREPAREd then EXECUTEd 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, writing instead:

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

Note that I've double-quoted "timestamp" as well, because it's a reserved word. It'll work without quotes in some contexts but not others. Choosing a data type name or reserved word as a column name is generally a bad idea.

answered Dec 04 '12 at 00:13

Craig Ringer

307,061
76
688
778

I tried to test the above correction but i got this error ERROR: function pg_catalog.timezone(unknown, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. – Mike Vasi Dec 04 '12 at 00:31
@MichelangeloVandilakis ... and how did you call it? Did you use setTimestamp to set the parameter? Consider editing your question and appending the updated code you tried. – Craig Ringer Dec 04 '12 at 00:33
@MichelangeloVandilakis Looking at the update, you don't seem to use `tz` anywhere. How are you determining the value in the DB? Java/JDBC query and if so, which one? `psql`? – Craig Ringer Dec 04 '12 at 01:08
I use pgadmin and i check the value. I didnt know where to use the tz variable in order to adjust the time zone :/ – Mike Vasi Dec 04 '12 at 01:21
I also changed the way i initialize the calendar using the preferable time zone – Mike Vasi Dec 04 '12 at 01:32

How to pass string with ' ' (timestamp) in prepared statement?

2 Answers2

Linked