4

Since daylight savings started here almost two weeks ago we have noticed that any queries on our new ColdFusion site that restrict dates in the following manner are returning incorrect data (where StartDate is in the format dd-mmm-yyyy).

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

We have found that if StartDate is altered as follows then 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 is set to UTC+10:00 with automatic adjustments for Daylight Saving Time enabled. The time setting in the Ingres II Visual Manager (II_TIMEZONE_NAME) is set to AUSTRALIA-VICTORIA.

We are using ColdFusion 10 with connections to an Ingres database via JDBC. Our old ColdFusion 4.5 server that uses an ODBC connection to the Ingres database does not suffer from this problem so I assume the problem we are having must be somehow related to either ColdFusion 10 or the JDBC connection we are now using.

Any ideas as to why this is happening? Why does a pure UTC date/time (i.e. with no time adjustment) need to be specified when doing something like that shown in the first example above?

Thanks.

Peter Boughton
  • 102,341
  • 30
  • 116
  • 172
jj2
  • 686
  • 1
  • 7
  • 16

1 Answers1

3

Assuming you're using a recent version of Ingres, 9.x and up, you might have to specify the timezone as a connection property/attribute. For example you can set the connection URL attribute TZ to AUSTRALIA-VICTORIA, i.e.

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

If Coldfusion supports JDBC properties the property name is timezone, the value is the same for TZ/II_TIMEZONE_NAME.

Whilst both ODBC and JDBC, ultimately connect to Ingres via the same interface, the JDBC is not aware of the server environment thus II_TIMEZONE_NAME is not observed.

grantc
  • 1,693
  • 12
  • 13
  • Hi Grant, thanks for your response. I have tried playing around with the TZ setting in the JDBC connection string but, unfortunately, it doesn't seem to be making any difference. In fact, I seem to be able to leave off the TZ setting completely and still get the same data so I'm wondering if it is being ignored for some reason. I'll play around a bit with the II_TIMEZONE_NAME setting in Ingres and see if that makes any difference. – jj2 Oct 22 '12 at 00:21
  • What if you set TZ to GMT? do you get the values 10 hours behind what they should be? – grantc Oct 22 '12 at 07:34