PostgreSQL PreparedStatement.setTimestamp question

Question

This project that I have is using a MySQL database. But now, I want to move to PostgreSQL. Everything works fine except for one table that has a column of type timestamp.

//MedName is varchar, and DateTime is timestamp
String INSERT_REFILL = "INSERT INTO refill (MedName, DateTime) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(INSERT_REFILL);
ps.setString(1, "Tylenol");
java.sql.Timestamp  sqlDate = new java.sql.Timestamp(new java.util.Date().getTime());
TimeZone tz = TimeZone.getTimeZone("GMT");
GregorianCalendar cal = (GregorianCalendar) GregorianCalendar.getInstance(tz);
ps.setTimestamp(2, sqlDate, cal);

My Question: For some reason, the setTimestamp always adds 5 hours to the values of the PreparedStatement. I'm in Central, so my time will be xx:xx:xx-0500 (Central) due to daylight savings. But I really doubt the +5 hours comes from that. Any ideas?

Note: I'm not the one who set up the PostgreSQL database but I created the table.

Jonathan Hall · Accepted Answer · 2011-07-06 00:17:09Z

2

As you say, U.S. Central time is 5 hours before UTC right now. (It's 6 hours before UTC during daylight savings time).

So you have a timezone conversion problem. It sounds like you're treating a timestamp in UTC as though it were in your local time.

You need to determine which timezone your input values are in, then input them accordingly to Postgres.

It may also be that Postgres is doing the conversion properly for you, and you're just reading the data incorrectly. It may be doing the local-to-UTC conversion exactly as it should, in which case you just need to convert from UTC back to local time when you extract the data for display.

answered Jul 6, 2011 at 0:17

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL PreparedStatement.setTimestamp question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related