SQL Date Interval Algorithm

Question

I am currently writing a Delphi application that runs queries over a DB2 database using ADO.

One of the requirements is that the user should be able to define queries using dates, for example "show me all data from the last 60 days" or "show me all data between November 20th 2009 and January 18th 2010". This wouldn't be an issue, except for two facts:

1. Dates are stored in the database using different fields for day, month and year.
2. The databases have been in use at several customer sites for a number of years and cannot be changed, and the application therefore has to run over the existing databases (so no changing the database to store dates in one field, which would make the task much simpler).

What I need to know is whether there is an efficient algorithm for constructing the SQL required to extract the specified information from the database. For example, today is January 18th, so to extract all information from November 20th to today, I would need an SQL statement that goes something like this:

SELECT data WHERE 
((day >= 20) AND (month = 11) AND (year = 2009)) OR 
((month = 12) AND (year = 2009)) OR 
((day <= 18) AND (month = 1) AND (year = 2010))

Obviously this is a trivial and relatively straightforward example, but if the user wanted to retrieve data from November 2008 instead of 2009, the query would get a lot bigger.

Is this the only way that I can construct the SQL statement, or is there a more efficient way to do this?

Answer 1

You could construct it to covert the year/month/day into a date data type, but that would preclude the use of indexes, so performance might be a problem.

Does db2 have calculated columns on which you could index and would you be able to add that? Or maybe a view which can have indexed/persisted calculated columns?

If you have indexes on the year/month/day though, the code that you have would probably be best for performance.

Answer 2

In DB2, the following should convert your separate columns into a date type: date(year||month||day)

If you have two dates, you can use subtraction to receive the number of days between the two, so your where clause can include

date(year||month||day) > CURRENT_DATE - 60

Answer 3

In MSSQL, I would try to write a function to return the number of days since a your data's date. In Firebird, I would do the same thing with a Delphi user defined function or at least a selectable stored procedure. I don't know if it is possible to extend the DB2 SQL with either of those two options, but if you could wrap the logic up in something like that, it would make your job much easier. I see mention of a DB2 SQL UDF in the Related posts on the right side of the page. I hope this is helpful.

Answer 4

since you want to compare to a date you should convert the non-date values to date first. That enables you to compare date against date and from there it's easy

Answer 5

The answer provided by Galghamon http://stackoverflow.com/questions/2087589/sql-date-interval-algorithm/2087712#2087712 is efficient in terms of code, but won't be able to leverage indexes.

I suggest you perform all necessary calculations in Delphi so that your logic can be conceptually reduced to one of the following:

(DateCol > @DateVal)
(DateCol >= @DateVal)
(DateCol < @DateVal)
(DateCol <= @DateVal)
(DateCol = @DateVal)

These can then be expanded for your rather unfortunate schema as follows (using 1st 2 examples):

(YearCol > @YearVal OR (YearCol = @YearVal AND MonthCol > @MonthVal) OR (YearCol = @YearVal AND MonthCol = @MonthVal AND DayCol > @DayVal))
(YearCol > @YearVal OR (YearCol = @YearVal AND MonthCol > @MonthVal) OR (YearCol = @YearVal AND MonthCol = @MonthVal AND DayCol >= @DayVal))

NOTE: You need to be precise about where to use inclusive and where to use exclusive inequality operators.

Answer 6

Regarding your remarks of the database changes. Consider adding a single column for the date. This column will only be written by a trigger and when at least one of the columns day, month, and year are updated. You have then the freedom of indexing this column and using it.

You won't impact any existing software and you have the convenience of the date column. Remember, the code you write today, need to be readable in 6 months by another developer.

So if you still can not make the db change consider Galghamon's solution, since it is easy to understand (low maintenance costs). Only go with Craig Young's solution if the performance impacts force you to do it.