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

Asked 3/12, 2012 at 2:0 Answered 3/12, 2012 at 18:50

postgresql timestamp prepared-statement timestamp-with-timezone value-of

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!

Wurster answered 3/12, 2012 at 2:0 Comment(0)

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

Naumann answered 3/12, 2012 at 18:50 Comment(7)

Shouldn't we include in the date format of to_timestamp() the timezone? – Wurster 3/12, 2012 at 20:31

@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. – Naumann 3/12, 2012 at 20:33

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 – Wurster 3/12, 2012 at 20:50

@MichelangeloVandilakis: that's the +2 in the value – Naumann 3/12, 2012 at 20:54

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') – Wurster 3/12, 2012 at 20:57

@MichelangeloVandilakis: no, apparently it's not necessary. I was a bit surprised as well when I tried. But there is actually not format "code" for that. But it does seem to work as far as I can tell – Naumann 3/12, 2012 at 21:3

let us continue this discussion in chat – Wurster 3/12, 2012 at 21:4

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

Babs answered 3/12, 2012 at 17:55 Comment(1)

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. – Wurster 3/12, 2012 at 17:59

Recommended topics

Hot tags