castor
  1. castor
  2. CASTOR-1276

Need a method for forcing the timezone for dates written to the database

    Details

    • Type: New Feature New Feature
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.9.9.1
    • Fix Version/s: 1.1.1
    • Component/s: JDO types
    • Labels:
      None
    • Number of attachments :
      9

      Description

      Castor has a method for forcing what timezone to read dates with. (See http://www.castor.org/types.html#SQL-Dates-and-Default-Timezone). The problem is that this setting has not effect on what you write to the database. So you don't have round-trip integrity of date, time and timestamp values.

      For example, if I set the timezone to be 'UTC' in the castor.properties. When I write a date to the database it will write it to the database as 'PDT' (my local timezone) cause this is what the JDBC driver defaults too. But when I read it back it will convert it to 'UTC' because castor is is only converting on reads. So my dates will be 8 hours off of what they should be. Anyway, not the clearest explanation...sorry. I've attached a modified SQLTypeInfos.java file that should address the issue.

      1. patch.c1276.20051202.txt
        1 kB
        Werner Guttmann
      2. patch.C1276.20051218.txt
        7 kB
        Werner Guttmann
      3. patch.C1276.20051218-002.txt
        34 kB
        Werner Guttmann
      4. patch.C1276.20051219.test.txt
        9 kB
        Werner Guttmann
      5. patch-C1276-20070130.txt
        11 kB
        Ralf Joachim
      6. patch-C1276-20070205.txt
        16 kB
        Ralf Joachim
      7. patch-C1276-20070307.txt
        33 kB
        Ralf Joachim
      8. SQLTypeInfos.java
        13 kB
        Brian Schlining
      9. TestTemplate.java
        10 kB
        Brian Schlining

        Issue Links

          Activity

          Hide
          Brian Schlining added a comment -

          Hi Ralf,

          Sorry in the delay getting back to you. Anyway when you ran the test case did you add this line to 'castor.properties':

          org.exolab.castor.jdo.defaultTimeZone=UTC

          If you do this the test case I provided should fail (assuming you aren't actually in the UTC timezone)

          Show
          Brian Schlining added a comment - Hi Ralf, Sorry in the delay getting back to you. Anyway when you ran the test case did you add this line to 'castor.properties': org.exolab.castor.jdo.defaultTimeZone=UTC If you do this the test case I provided should fail (assuming you aren't actually in the UTC timezone)
          Hide
          Ralf Joachim added a comment -

          Now that you mention the timezone setting in castor.properties I know what I missed Will come back with test updated result soon.

          Show
          Ralf Joachim added a comment - Now that you mention the timezone setting in castor.properties I know what I missed Will come back with test updated result soon.
          Hide
          Ralf Joachim added a comment -

          Hi Brian,

          after trying lots of different things I finaly managed to reproduce the problem and also verified that the solution is valid. The reason for my problems was that mysql driver seams to be a bit more inteligent with regard to timezones, as this driver corrects the timezone mismatch himself. As I have no access to microsoft sql server I used maxdb instead which showed the same problem you have with the microsoft one.

          After cleanup and improvement of the test case I'll commit the patch. Having said that I don't think we can integrate the test case with our suite at the moment as we have no possibility to change properties for a single test.

          Show
          Ralf Joachim added a comment - Hi Brian, after trying lots of different things I finaly managed to reproduce the problem and also verified that the solution is valid. The reason for my problems was that mysql driver seams to be a bit more inteligent with regard to timezones, as this driver corrects the timezone mismatch himself. As I have no access to microsoft sql server I used maxdb instead which showed the same problem you have with the microsoft one. After cleanup and improvement of the test case I'll commit the patch. Having said that I don't think we can integrate the test case with our suite at the moment as we have no possibility to change properties for a single test.
          Hide
          Brian Schlining added a comment -

          Hi Ralf,

          Thanks for going through the contortions deed to run this test. This bug has been impacting me in a very painful ways, so I'm very relieved that you'll commit the patch.

          BTW, I have run this same test on Apache Derby, Oracle 10g and XE as well as SQL Server 2000 and they have all exhibited this time zone issue. That's very interesting about the MySQL driver though; I'll have to remember that.

          Show
          Brian Schlining added a comment - Hi Ralf, Thanks for going through the contortions deed to run this test. This bug has been impacting me in a very painful ways, so I'm very relieved that you'll commit the patch. BTW, I have run this same test on Apache Derby, Oracle 10g and XE as well as SQL Server 2000 and they have all exhibited this time zone issue. That's very interesting about the MySQL driver though; I'll have to remember that.
          Hide
          Ralf Joachim added a comment -

          Final patch for review.

          It need to be noted that I had to remove force of timezone for reading and writing SQL dates to omit problems. For SQL time and timestamp Castor will now allow to force a timezone. Still we need to find a solution to force timezone if date values are stored as long or string but that's another issue.

          Show
          Ralf Joachim added a comment - Final patch for review. It need to be noted that I had to remove force of timezone for reading and writing SQL dates to omit problems. For SQL time and timestamp Castor will now allow to force a timezone. Still we need to find a solution to force timezone if date values are stored as long or string but that's another issue.

            People

            • Assignee:
              Ralf Joachim
              Reporter:
              Brian Schlining
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: