24

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!

Mike Vasi
  • 467
  • 2
  • 5
  • 16

2 Answers2

12

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");
  • Shouldn't we include in the date format of to_timestamp() the timezone? – Mike Vasi Dec 03 '12 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. –  Dec 03 '12 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 – Mike Vasi Dec 03 '12 at 20:50
  • @MichelangeloVandilakis: that's the `+2` in the value –  Dec 03 '12 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') – Mike Vasi Dec 03 '12 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 –  Dec 03 '12 at 21:03
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20530/discussion-between-michelangelo-vandilakis-and-a-horse-with-no-name) – Mike Vasi Dec 03 '12 at 21:04
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

mariosk89
  • 944
  • 1
  • 11
  • 31
  • 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. – Mike Vasi Dec 03 '12 at 17:59