Dates sometimes off by a day?

[saggio]

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:

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):

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...

[JosAH]

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

[saggio]

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

[JosAH]

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

[saggio]

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:

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?

[Tolls]

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.

[saggio]

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).

[Tolls]

OK.
Is there a timezone on the database?

What does your insert statement look like?

[saggio]

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:

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;
		}

[Tolls]

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.

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

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?

[saggio]

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?

[Tolls]

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

[saggio]

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

@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

<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:

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?

[Tolls]

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.