Is java.sql.Timestamp timezone specific?

by

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store “2012-12-25 10:00:00 UTC”. The actual value stored in the database is “2012-12-25 12:00:00”. You retrieve it again: you get it back again as “2012-12-25 10:00:00 UTC” (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as “2012-12-25 12:00:00 UTC”.

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

More Related Contents:

  1. java.util.Date vs java.sql.Date
  2. java.sql.SQLException: – ORA-01000: maximum open cursors exceeded
  3. Using setDate in PreparedStatement
  4. Java 8: Difference between two LocalDateTime in multiple units
  5. Handling MySQL datetimes and timestamps in Java
  6. Why do I get java.lang.AbstractMethodError when trying to load a blob in the db?
  7. Calendar returns date in wrong time zone
  8. How to call oracle stored procedure which include user-defined type in java?
  9. How to subtract X day from a Date object in Java?
  10. Get yesterday’s date using Date [duplicate]
  11. Java: Convert String to TimeStamp
  12. How to get the first date and last date of the previous month? (Java)
  13. Oracle JDBC intermittent Connection Issue
  14. How can I “pretty print” a Duration in Java?
  15. Getting output from dbms_output.get_lines using JDBC
  16. Get integer value of the current year in Java
  17. JDBC ResultSet get columns with table alias
  18. Timezones in SQL DATE vs java.sql.Date
  19. Java JDBC – How to connect to Oracle using tnsnames.ora
  20. ojdbc14.jar vs. ojdbc6.jar
  21. How to format date string in java? [duplicate]
  22. Convert LocalDate in DD/MM/YYYY LocalDate [duplicate]
  23. How to convert TimeStamp to Date in Java?
  24. Connection timeout for DriverManager getConnection
  25. Oracle’s RETURNING INTO usage in Java (JDBC, Prepared Statement)
  26. Connection cannot be cast to oracle.jdbc.OracleConnection
  27. Java 8: Calculate difference between two ZonedDateTime
  28. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  29. The Network Adapter could not establish the connection when connecting with Oracle DB
  30. Date format conversion using Java

Leave a Comment