Results 1 to 14 of 14
  1. #1
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default Dates sometimes off by a day?

    Hi all,

    I have an application that reads header information from an image file and populates fields (such as patient name, DoB, StudyDate, etc) and then uploads the information onto a SQL server.

    However, something is going amiss between extracting the header information and putting into the SQL server. The real Date of Birth (from the image header) is often a day AFTER the DoB that is put into the SQL table. There doesn't seem to be any rhyme or reason as to when it gets the right DoB (i.e. what time of day the images are uploaded), except that many more wrong entries have been uploaded after DST ended (November 7th).

    I'm pretty certain that it has to be a timezone issue, but I can't seem to put my finger on it. No other dates that are used are wrong (The date the image was taken, the date the image was uploaded to the server), just the DoB seems to be having issues.

    Here's the code that extracts the DoB from the image:

    Java Code:
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    				try {
    					data.birthDate = sdf.parse(date.toDICOMString());
    				} catch (Exception ex) {
    					data.birthDate = null;
    				}
    The only difference between the other dates and the DoB is that the other times include the timestamp as well as the day as well.

    Thanks!

    EDIT: I just looked, and the program pulls the DoB first, and then the exam date second (both using the same Ddate variable). Here's that snippet of code (which comes right after the above code):

    Java Code:
    date = (DDate) dcmData.get(DDict.dStudyDate);
    			if (date == null) {
    				data.studyDate = null;
    			} else {
    				String time = date.toDICOMString() + dcmData.getS(DDict.dStudyTime);
    				data.studyDate = parseDicomDateTime(time);
    			}
    Could it possibly be using the studyTime and injecting into the DoB (since it has a null value for time) because it's using the same variable? I don't think it would, but that might explain it...
    Last edited by saggio; 12-07-2010 at 06:33 PM.

  2. #2
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,371
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by saggio View Post
    There doesn't seem to be any rhyme or reason as to when it gets the right DoB (i.e. what time of day the images are uploaded), except that many more wrong entries have been uploaded after DST ended (November 7th).
    Are you sure it's not a daylight saving time issue? As far as I know DST ends in the last weekend of October, not somewhere in November.

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  3. #3
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    That's what I thought as well, but apparently it was extended a couple years ago :p

    Daylight Saving Time - Overview of Daylight Saving Time

  4. #4
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,371
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by saggio View Post
    That's what I thought as well, but apparently it was extended a couple years ago :p

    Daylight Saving Time - Overview of Daylight Saving Time
    DST is a mess; look here and here. I don't know what TZ (Time Zone) you're in but I still think it can be the cause of your problems.

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  5. #5
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    Thanks for the info.

    I found some more code that might be the culprit of this whole DST mess.

    Apparently, after the information is extracted from the image, it sets a calendar to the birthdate:

    Java Code:
    if (data.birthDate != null) {
    				GregorianCalendar cal = new GregorianCalendar();
    				cal.setTime(data.birthDate);
    				if (cal.get(Calendar.YEAR) < 1753) // Earliest valid SQL year
    				{
    					lastError += ERR_DOB;
    				}
    			}
    Could this be it? I was reading up on the GregorianCalendar and it said that it is sensitive to DST.

    Also, could there be an issue with timezone differences (on top of dst)? I.e. the client is being run somewhere in europe but the server is in EST? Could that also cause discrepancies in times/dates?

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,855
    Rep Power
    19

    Default

    Where is this problem appearing?
    Have you stuck debug statements in to see what values you have for the date, prior to parsing, after parsing and after creating the calendar, the value inserted into the db?

    So far you haven't actually said where you are seeing the problem.

  7. #7
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    ok, sorry about the vagueness before.

    Apparently it is extracting the data from the image fine and what is being sent to the database is the correct date. However, what shows up in the database is off by a day (a day earlier than it should be).

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,855
    Rep Power
    19

    Default

    OK.
    Is there a timezone on the database?

    What does your insert statement look like?

  9. #9
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    Okay, I did some investigation into the timezone settings:


    The client machines are set to GMT-5, as are the production servers. The DateOfBirths to show up as a day prior, being born at 23:00 (instead of the correct date at 00:00, so a 1 hour difference).

    The thought was that it had to do with rolling back the clocks for DST, although it seems to have started to occur on 11/4, 3 days prior to the ending of DST.

    However, the odd thing is that the test servers are set to GMT-8, and the date of births are showing up correctly when uploaded to that DB.

    the same bug seems to always be there...there was an issue of DoB's showing up with a time of 7:00 or 8:00 (no times are ever associated with DoB's so they should always be 00:00) back in the summer.

    Here's the code that executes the sql insert statement:

    Java Code:
    public int execUpdate( ) throws Exception, java.sql.SQLException
    	{
    		Connection con = null;
    		Statement stmt = null;
    		System.out.println( statement );
    
    		try
    		{
    			// run the query
    			con = getConnection();
    			stmt = con.createStatement(); // create statement
    			int numRows = stmt.executeUpdate( statement ); // execute statment
    
    			return numRows;
    		}
    		catch ( Exception e )
    		{
    			e.printStackTrace();
    			throw e;
    		}

  10. #10
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,855
    Rep Power
    19

    Default

    Quote Originally Posted by saggio View Post
    However, the odd thing is that the test servers are set to GMT-8, and the date of births are showing up correctly when uploaded to that DB.

    the same bug seems to always be there...there was an issue of DoB's showing up with a time of 7:00 or 8:00 (no times are ever associated with DoB's so they should always be 00:00) back in the summer.
    Well that one is clearly timezone since it's 8 (or so) hours out. A Date object is merely a long number of milliseconds from the start of the epoch so it always has a time associated with it, whether you want it to or not.

    Quote Originally Posted by saggio View Post
    Here's the code that executes the sql insert statement:

    Java Code:
    public int execUpdate( ) throws Exception, java.sql.SQLException
    	{
    		Connection con = null;
    		Statement stmt = null;
    		System.out.println( statement );
    
    		try
    		{
    			// run the query
    			con = getConnection();
    			stmt = con.createStatement(); // create statement
    			int numRows = stmt.executeUpdate( statement ); // execute statment
    
    			return numRows;
    		}
    		catch ( Exception e )
    		{
    			e.printStackTrace();
    			throw e;
    		}
    That's a Statement. Are you saying you're concatenating your date into an INSERT? If so then that could also be your problem. How is that statement created?

  11. #11
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    I'm currently looking to see where exactly that SQL statement is being created. I apologize, I'm new to this program and am still learning my way around all of the classes.

    However, I did just try to manually insert dates (with no time, just like the real DoB being extracted from the image) into the SQL table and they showed up with the correct date/time. Is that a good indicator that it does have something to do with how the sql insert statement is being created rather than a DB problem?

  12. #12
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,855
    Rep Power
    19

    Default

    Possibly.
    I really would look at the SQL.
    In fact, you could just log it in that update method.

  13. #13
    saggio is offline Member
    Join Date
    Dec 2010
    Posts
    9
    Rep Power
    0

    Default

    Here's where the table is being created (just clipping the DoB column):

    Java Code:
    @Entity( name = "PatientDemographics" )
    @Table( catalog = "IDC", schema = "dbo", name = "PatientDemographics" )
    public class PatientDemographics extends AbstractVO<PatientDemographics>
    {
    ...
    @Basic
    	@Column( name = "BirthDateTime", length = 23, precision = 3 )
    	public Timestamp getBirthDateTime()
    	{
    		return birthDateTime;
    	}
    
    	public void setBirthDateTime( Timestamp birthDateTime )
    	{
    		this.birthDateTime = birthDateTime;
    	}
    And here's the .hbm file

    Java Code:
    <hibernate-mapping>
        <class name="com.patient.patientinfo" table="PatientDemographics">
            <id name="patientNumId" type="integer" column="PatientNumId"/>
            <property name="dateOfBirth" type="date" column="BirthDateTime"/>
        </class>
    </hibernate-mapping>
    which creates this:

    Java Code:
    public class PatientDemographics  implements java.io.Serializable {
    
    
         private Integer patientNumId;
         private Date dateOfBirth;
    ...
    public PatientDemographics(Integer patientNumId, Date dateOfBirth) {
           this.patientNumId = patientNumId;
           this.dateOfBirth = dateOfBirth;
        }
    ...
    public Date getDateOfBirth() {
            return this.dateOfBirth;
        }
        
        public void setDateOfBirth(Date dateOfBirth) {
            this.dateOfBirth = dateOfBirth;
        }
    }
    still looking to see where it's sending that data to actually construct the sql statement...but nothing looks screwy with the above, does it?

  14. #14
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,855
    Rep Power
    19

    Default

    Since this is Hibernate, why the execUpdate() method?
    Hibernate does it all for you.
    In fact, Hibernate tends to get unhappy if you do stuff behind its back.

Similar Threads

  1. comparing dates
    By palls in forum New To Java
    Replies: 3
    Last Post: 08-13-2010, 01:08 PM
  2. UTC Dates
    By PedroCosta in forum Advanced Java
    Replies: 3
    Last Post: 04-01-2010, 06:08 PM
  3. calculating with dates
    By hannes in forum New To Java
    Replies: 1
    Last Post: 01-14-2010, 08:22 PM
  4. Help with dates
    By bumblyb33 in forum New To Java
    Replies: 1
    Last Post: 02-13-2009, 02:54 PM
  5. differences between 2 dates
    By cecily in forum New To Java
    Replies: 1
    Last Post: 08-02-2007, 05:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •