How can i insert timestamp with timezone in postgr

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!

Answer 1

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

Answer 2

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