How can i insert timestamp with timezone in postgresql with prepared statement?

Question

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.

That is the exact code that causes the error:

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!

user330315 · Accepted Answer · 2012-12-03 18:50:52Z

14

The basic problem is that a java.sql.Timestamp does not contain timezone information. I think it is always assumed to be "local timezone".

On solution I can think of is to not use a parameter in a PreparedStatement, but a timezone literal in 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():

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

answered Dec 3, 2012 at 18:50

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

Mike Vasi Over a year ago

Shouldn't we include in the date format of to_timestamp() the timezone?

user330315 Over a year ago

@MichelangeloVandilakis: it is included in the second example (it's right after the ? placeholder). The first example is not a call to to_timstamp() but a timestamp literal which has to follow specific rules.

Mike Vasi Over a year ago

maybe i didn't write it right. I mean that the format includes only the year,month,day hours,minutes,seconds but nothing to show that timezone is included

user330315 Over a year ago

@MichelangeloVandilakis: that's the +2 in the value

Mike Vasi Over a year ago

yes i unsterdand that but i dont mean in the format of the setString() but in the format of the second field of to_timestamp(). For example, to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss Z')

|

mariosk89 · Accepted Answer · 2012-12-03 17:55:08Z

0

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

answered Dec 3, 2012 at 17:55

mariosk89

9541 gold badge12 silver badges33 bronze badges

1 Comment

Mike Vasi Over a year ago

Ok this is an acceptable approach but i would like to know if there is any way of doing it by the afored mentioned way.

Collectives™ on Stack Overflow

How can i insert timestamp with timezone in postgresql with prepared statement?

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related