Incorrect data with Coldfusion, SQL, and Ingres since daylight saving time

Question

Since daylight savings started here almost two weeks ago, we noticed that any queries on our new ColdFusion website that limit dates as follows return incorrect data (where StartDate is in dd-mmm-yyyy format).

select ... from ... where ... and o.booking_date >= date('#StartDate#') and o.booking_date < date('#StartDate#') + date('1 day') 

We found that if the StartDate value changes as follows, the correct data is returned:

 and booking_date >= '#DateFormat(DateAdd("d",-1,StartDate), "dd-mmm-yyyy")# 13:00' and booking_date < '#DateFormat(StartDate, "dd-mmm-yyyy")# 13:00' 

The time on our CF server is correct and set to UTC + 10: 00 with automatic settings for the switched daylight saving time. The time setting in Visual Manager (II_TIMEZONE_NAME) for Ingres II is set to AUSTRALIA-VICTORIA.

We use ColdFusion 10 with a connection to the Ingres database through JDBC. Our old ColdFusion 4.5 server, which uses an ODBC connection to the Ingres database, does not suffer from this problem, so I assume that the problem we have should be somehow related to the ColdFusion 10 or JDBC connection that we are now use.

Any ideas as to why this is happening? Why should the date / time of pure UTC be indicated (i.e., without adjusting the time) when you do something similar to the one shown in the first example above?

Thanks.

Answer 1

Assuming you are using the latest version of Ingres, 9.x and later, you may need to specify the time zone as a property / attribute of the connection. For example, you can set the TZ connection URL attribute to AUSTRALIA-VICTORIA , i.e.

jdbc:ingres://..../mydb;TZ=AUSTRALIA-VICTORIA

If Coldfusion supports JDBC properties, the name of the timezone property, then the value is equal to TZ / II_TIMEZONE_NAME.

While ODBC and JDBC ultimately connect to Ingres through the same interface, JDBC is not aware of the server environment, so II_TIMEZONE_NAME is not observed.