3 Replies Latest reply on Apr 30, 2003 3:32 PM by 807546

    SimpleDateFormater and MySql date errors

    807546
      I've looked around in the forums for an answer to this, but failed to find anything.

      I'm loading a date from a MySql (v 3.22) table and using SimpleDateFormatter to format it before I make an XML call to push it to the server. The formatter appears to fail on average 4 out of 11,000 times. The failure is "random" year, month, day, hour, minute or second. Sometimes just one of these is bad, sometimes multiples.

      The end result is a bad date record on the server and no record of it on the client. No exceptions are thrown.

      Not sure if this has anything to do with the error, but I've found that 80% of the errors occur the first time the SimpleDateFormatter is called.

      Here's some examples of what is bad vs real (yyyy-mm-dd+hh:mm:ss):

      2003-03-28+12:53:12 2003-03-25+12:53:12
      2003-03-30+07:54:38 2003-03-22+07:54:38
      2008-06-25+07:31:57 2003-03-22+07:31:57
      2003-04-21+05:12:17 2003-03-22+05:12:17
      2003-04-23+12:35:54 2003-03-21+12:35:54
      2003-04-23+07:32:49 2003-03-21+07:32:49

      Here's some snippits:

      // this gets the date from the recordset.
      // I have to typecast the date because of java.sql.Date vs java.util.Date.

      java.util.Date d = (java.util.Date) recordSet.getTimestamp("InDate");

      // Here's the method called to convert and format the date

      public static String fixDateForCorp(java.util.Date d)
      {if (d == null) return null;
      String s = null;
      SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd+HH:mm:ss");
      formatter.setTimeZone(java.util.TimeZone.getDefault());
      try
      {s = formatter.format(d)
      }
      catch (Exception e)
      {// this really goes to a log
      System.out.println("ERROR, fixDateForCorp:Exception:" + e.getMessage());
      return null;
      }
      return (s);
      } // getDateTimeStr

      Thanks in advance,
      Bit
        • 1. Re: SimpleDateFormater and MySql date errors
          DrClap
          ... I've found that 80% of the errors occur the first time the SimpleDateFormatter is called.
          But each call to fixDateForCorp creates a new SimpleDateFormat, which is only used once, so I don't understand what that means.

          However, just a couple of observations that may or may not be useless:

          Your examples are all off by an exact number of days; not by an exact multiple of 24 hours, since there would be a daylight-saving change between the "bad" and "good" versions. (At least there is in my timezone, maybe not in yours.)

          It is the MySQL JDBC driver that is creating those Timestamp objects, so it is possible that is where the error occurs.

          SimpleDateFormat is not thread-safe; are there multiple threads involved here?
          • 2. Re: SimpleDateFormater and MySql date errors
            807546
            Thanks for responding DrClap.
            But each call to fixDateForCorp creates a new SimpleDateFormat
            Yes, I'm grasping for straws on this one.
            Your examples are all off by an exact number of days;
            Here's some more examples, Sorry bad sample of data the first time.

            2005-03-25+20:07:28 2003-03-29+10:27:28
            2003-03-23+13:56:44 2003-03-29+14:17:44
            2003-03-26+22:28:11 2003-03-29+14:34:11
            2003-03-30+15:40:40 2003-03-31+14:34:40
            It is the MySQL JDBC driver that is creating those Timestamp objects, so it is possible that is where the error occurs.
            Interisting idea. For fun I did a toString() on the date that MySql returns, to my suprise it looked like this:

            toString:2003-04-01 14:50:08.0

            Leading zeros, just replace the space with the "+", kill the milliseconds and that should do it. Not using the formatter at all. I'm going to try this and it will nail the driver if it continues to fail.
            SimpleDateFormat is not thread-safe; are there multiple threads involved here?
            A flag is set to determine if the record is sent and persists the data in the MySql table. Periodically, a record set of unreplicated records is retrieved and sent to the server. I have to create a new url for each push in the record set so I'm pretty certain that multiples are not called. I've written a thread handler as well to prevent multiple calls of the replication methods.

            I'm going to try the toString() method for creating the string. I'll post here the results for anyone else having this problem. Will be a month or so before it's distributed though.

            Thanks again,
            Bit
            • 3. Re: SimpleDateFormater and MySql date errors
              807546
              This is a post on how I fixed the problem and possible cause.

              The date formatter was not the problem at all. I suspect it is the MySql driver or a database collision causing the wierdness. I tried to replicate the problem to no avail.

              To fix this, I used the toString() function of the date object returned. After conversion, I sent a select/count/where statement to the database using the string and counted for one record and if successfull, I sent it. So far using this method, no odd dates have been sent.

              Thanks to all who helped me with this.