Is java sql Timestamp timezone specific

Question

I have to store UTC dateTime in DB  I have converted the dateTime given in specific timezone to UTC  for that I followed the below code  My input dateTime is  20121225 10 00 00 Z  timezone is  Asia Calcutta   My Server DB oracle  is running in the same timezone IST   Asia Calcutta    Get the Date object in this specific Timezone          String date    20121225 10 00 00 Z           String timeZoneId    Asia Calcutta           TimeZone timeZone   TimeZone getTimeZone timeZoneId            DateFormat dateFormatLocal   new SimpleDateFormat  yyyyMMdd HH mm ss z                          This date object is given time and given timezone         java util Date parsedDate   dateFormatLocal parse date                                    timeZone getDisplayName false  TimeZone SHORT             if  timeZone inDaylightTime parsedDate                    We need to re-parse because we don t know if the date                is DST until it is parsed                parsedDate   dateFormatLocal parse date                             timeZone getDisplayName true  TimeZone SHORT                        assigning to the java sql TimeStamp instace variable         obj setTsSchedStartTime new java sql Timestamp parsedDate getTime        Store into DB          if  tsSchedStartTime    null                stmt setTimestamp 11  tsSchedStartTime             else               stmt setNull 11  java sql Types DATE               OUTPUT  DB  oracle  has stored the same given dateTime   20121225 10 00 00 not in UTC   I have confirmed from the below sql        select to char sched start time   yyyy mm dd hh24 mi ss   from myTable   My DB server also running on the same timezone  Asia Calcutta   It gives me the below appearances    Date getTime   is not in UTC  Or Timestamp is has timezone impact while storing into DB What am I doing wrong here    One more question   Will timeStamp toString   print in local timezone like java util date does  Not UTC

User · Answer

The answer is that java sql Timestamp is a mess and should be avoided  Use java time LocalDateTime instead  So why is it a mess  From the java sql Timestamp JavaDoc  a java sql Timestamp is a  quot thin wrapper around java util Date that allows the JDBC API to identify this as an SQL TIMESTAMP value quot   From the java util Date JavaDoc   quot the Date class is intended to reflect coordinated universal time  UTC  quot   From the ISO SQL spec a TIMESTAMP WITHOUT TIME ZONE  quot is a data type that is datetime without time zone quot   TIMESTAMP is a short name for TIMESTAMP WITHOUT TIME ZONE  So a java sql Timestamp  quot reflects quot  UTC while SQL TIMESTAMP is  quot without time zone quot   Because java sql Timestamp reflects UTC its methods apply conversions  This causes no end of confusion  From the SQL perspective it makes no sense to convert a SQL TIMESTAMP value to some other time zone as a TIMESTAMP has no time zone to convert from  What does it mean to convert 42 to Fahrenheit  It means nothing because 42 does not have temperature units  It s just a bare number  Similarly you can t convert a TIMESTAMP of 2020-07-22T10 38 00 to Americas Los Angeles because 2020-07-22T10 30 00 is not in any time zone  It s not in UTC or GMT or anything else  It s a bare date time  java time LocalDateTime is also a bare date time  It does not have a time zone  exactly like SQL TIMESTAMP  None of its methods apply any kind of time zone conversion which makes its behavior much easier to predict and understand  So don t use java sql Timestamp  Use java time LocalDateTime  LocalDateTime ldt   rs getObject col  LocalDateTime class   ps setObject param  ldt  JDBCType TIMESTAMP

User · Answer

It is specific from your driver   You need to supply a parameter in your Java program to tell it the time zone you want to use   java -Duser timezone  America New York  GetCurrentDateTimeZone   Further this     to char new time sched start time   CURRENT TIMEZONE    NEW TIMEZONE     MM DD YY HH MI AM     May also be of value in handling the conversion properly   Taken from here

User · Answer

You can use the below method to store the timestamp in database specific to your desired zone zone Id  ZonedDateTime zdt   ZonedDateTime now ZoneId of  quot Asia Calcutta quot      Timestamp timestamp   Timestamp valueOf zdt toLocalDateTime      A common mistake people do is use LocaleDateTime to get the timestamp of that instant which discards any information specif to your zone even if you try to convert it later  It does not understand the Zone  Please note Timestamp is of the class java sql Timestamp

User · Answer

For Mysql  we have a limitation  In the driver Mysql doc  we have       The following are some known issues and limitations for MySQL   Connector J  When Connector J retrieves timestamps for a daylight   saving time  DST  switch day using the getTimeStamp   method on the   result set  some of the returned values might be wrong  The errors can   be avoided by using the following connection options when connecting   to a database    useTimezone true useLegacyDatetimeCode false serverTimezone UTC   So  when we do not use this parameters and we call setTimestamp or getTimestamp with calendar or without calendar  we have the timestamp in the jvm timezone   Example    The jvm timezone is GMT 2  In the database  we have a timestamp   1461100256   19 04 16 21 10 56 000000000 GMT  Properties props   new Properties    props setProperty  user    root    props setProperty  password        props setProperty  useTimezone    true    props setProperty  useLegacyDatetimeCode    false    props setProperty  serverTimezone    UTC    Connection con   DriverManager getConnection conString  props          Calendar nowGMT   Calendar getInstance TimeZone getTimeZone  GMT     Calendar nowGMTPlus4   Calendar getInstance TimeZone getTimeZone  GMT 4            rs getTimestamp  timestampColumn     Oracle driver convert date to jvm timezone and Mysql convert date to GMT  specified in the parameter  rs getTimestamp  timestampColumn   nowGMT    convert date to GMT  rs getTimestamp  timestampColumn   nowGMTPlus4    convert date to GMT 4 timezone   The first method returns    1461100256000   19 04 2016 - 21 10 56 GMT  The second method returns   1461100256000   19 04 2016 - 21 10 56 GMT  The third method returns    1461085856000   19 04 2016 - 17 10 56 GMT  Instead of Oracle  when we use the same calls  we have    The first method returns    1461093056000   19 04 2016 - 19 10 56 GMT  The second method returns   1461100256000   19 04 2016 - 21 10 56 GMT  The third method returns    1461085856000   19 04 2016 - 17 10 56 GMT  NB   It is not necessary to specify the parameters for Oracle

User · Answer

I think the correct answer should be java sql Timestamp is NOT timezone specific  Timestamp is a composite of java util Date and a separate nanoseconds value  There is no timezone information in this class  Thus just as Date this class simply holds the number of milliseconds since January 1  1970  00 00 00 GMT   nanos    In PreparedStatement setTimestamp int parameterIndex  Timestamp x  Calendar cal  Calendar is used by the driver to change the default timezone  But Timestamp still holds milliseconds in GMT    API is unclear about how exactly JDBC driver is supposed to use Calendar  Providers seem to feel free about how to interpret it  e g  last time I worked with MySQL 5 5 Calendar the driver simply ignored Calendar in both PreparedStatement setTimestamp and ResultSet getTimestamp

User · Answer

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

[java] Is java.sql.Timestamp timezone specific?

Examples related to java

Examples related to oracle

Examples related to date

Examples related to datetime

Examples related to jdbc