Using Records to avoid problems with database date/time interval

Let's share the knowledge with your friends
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

A record (analogous to a structure in some languages) represents a heterogeneous set of elements. Each element is called a field; the declaration of a record type specifies a name and type for each field.
The record type became more powerful in Delphi 2006 by adding support for:

  • Constructors
  • Non-virtual methods
  • Static methods and properties
  • and more.

Though records can now share much of the functionality of classes, there are some important differences between classes and records.

  • Records do not support inheritance.
  • Records can contain variant parts; classes cannot.
  • Records are value types, so they are copied on assignment, passed by value, and allocated on the stack unless they are declared globally or explicitly allocated using the New and Dispose function. Classes are reference types, so they are not copied on assignment, they are passed by reference, and they are allocated on the heap.
  • For more, check your RAD Studio documentation.

Record can help us in many different situations, for example in case you need to represent a database primary key, we can use Record for that helping to easy understand and maintain the code. It’s true you don’t keep changing your primary keys very frequently, but some day if it became necessary, it will facilitate the necessary changes. Another situation is to use Record to represent data interval and I will talk about that in this post.

Imagine the situation where you need to run a sales report filtered by period, and you have the follow method for that.

    procedure GenerateReport( InitialDate, FinalDate : TDateTime );

During my trips visiting customers, many of them report a common problem when we talk about date interval, specially when is related with reports. The reason for this problem is because the DATE value on the DATE field includes TIME and the applications just set the Date for the interval.

For many years the databases saved date and time on the same field, later they start to provide Data, Time and TimeStamp date types. Because the amount of data, systems and integration we can’t make update our database to represent this new data types, beside that those changes will impact many application. Considering we can’t make changes in our database, many of the Delphi Developers develop application to support more than one database, our application need to provide a mechanism to deal easily with this situation. We just need to make sure the date interval values will have ’00:00:00′ for the initial date and ’23:59:59′ final date, each database handle format for date/time differently.

Record is a excellent solution for this problem, instead to use two parameters for the method GenerateReport we will represent both as one Record and it will take care of the date/time interval values, making easy to read, understand and maintain the code.

The new method signature will change for::

    procedure GenerateReport( Interval : TDateInterval );

The record structure will include the properties StartDate and FinalDate, the Set methods will adjust the time value for both dates.

Below the record declaration.

unit Interval;

interface

uses SysUtils, DateUtils;

type

  TDateInterval = Record
  private
    FFinalDate: TDateTime;
    FStartDate: TDateTime;
    procedure SetFinalDate(const Value: TDateTime);
    procedure SetStartDate(const Value: TDateTime);

  public
    property StartDate: TDateTime read FStartDate write SetStartDate;
    property FinalDate: TDateTime read FFinalDate write SetFinalDate;

    Constructor Create(Id, Fd: TDateTime);

    procedure SetAnnualInterval( Iy, Fy : Integer );
  end;

implementation

{ TDateInterval }

constructor TDateInterval.Create(SYear, FYear: TDateTime);
begin
  StartDate := SYear;
  FinalDate := FYear;
end;

procedure TDateInterval.SetFinalDate(const Value: TDateTime);
begin
  FFinalDate := EncodeDateTime(Yearof(Value), MonthOf(Value), Dayof(Value), 23, 59, 59, 999);
end;

procedure TDateInterval.SetStartDate(const Value: TDateTime);
begin
  FStartDate := EncodeDateTime(Yearof(Value), MonthOf(Value), Dayof(Value), 0, 0, 0, 0);
end;

procedure TDateInterval.SetAnnualInterval(SYear, FYear: Integer);
begin
  StartDate := EncodeDate(SYear, 1, 1);
  FinalDate := EncodeDate(FYear, 12, 31);
end;

end.
  • Any changes on StartDate and FinalDate properties will be adjusted to represent the correct time values
  • There is a additional method named SetAnnualInterval, where you pass the period of years you would like for the reports, instead of the complete dates.

The sample below shows how you can use this Record to present the interval;

var
  interval: TDateInterval;
begin
  interval.StartDate := EncodeDate( 2009, 1, 1);
  interval.FinalDate := Now;

  GenerateReport(interval);

Another way is to pass the interval using the Record constructor.

var
  interval: TDateInterval;
begin
  interval.Create(EncodeDate( 2009, 1, 1), Now);
  GenerateReport(interval);

Also you can use the method SetAnnualInterval, where you specify a annual interval.

var
  interval : TDateInterval;
begin

  interval.SetAnnualInterval(2008, 2009);

  GenerateReport(interval);

In all cases the time was adjusted by the Set Methods,

We can add another features in this record, why note ask for the record to create the SQL where clause? Just two additional methods will provide that for us.

DBDateFormat formats the date and time values to add on the where clause. If the database uses different format, which I’m not representing the date/time format for every database on the following code, but after you read this post, you can download the source code and make the changes necessary to represent the database format. An additional parameter could be enough for this method, you can pass the SQL Connection for example and look at the driver property to identify how to format the date/time values. In this sample I don’t use parameter, but you can update the code later.

The method GenerateSQL has the Field parameter, which represents the table field.

function TDateInterval.DBDateFormat(const Value: TDateTime): String;
begin
  Result := FormatDateTime('mm/dd/yyyy hh:mm:ss', Value);
end;

function TDateInterval.GenerateSQL(Field: String): String;
Const
  sql: String = '%s between ''%s'' and ''%s'' ';
begin
  Result := Format(sql, [Field, DataDBFormat(StartDate), DataDBFormat(FinalDate)]);
end;

You use both method like this:

var
  period : TDateInterval;
begin

  period.SetAnnualInterval(2008, 2009);

  ShowMessage( period.GenerateSQL('DATE_FIELD'));

The ShowMessage will show DATE_FIELD between ’01/01/2008 00:00:00′ and ’12/31/2009 23:59:59′

I hope this visualizes another way to use Record, and that you find the date interval example useful. It can of course be implemented in many different ways, this is just one which I use to help in my explanation about Records.

You can download the source code here

TDateInterval

Let's share the knowledge with your friends
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
9 replies
  1. Colin
    Colin says:

    Hi Andreano,

    Excellent article! I had a similar problem but had to handle open-ended date ranges. I was going to tackle this by creating it as a class but making it as a record makes a lot more sense. Thank you for providing articles for not only on the “headlne” features but also for the small “trivial” features too 🙂

    Colin

    Reply
  2. Istvan
    Istvan says:

    Hi.
    The above EncodeDateTime will raise an exception because 1000ms is not valid and should be set to 999. A better approach would be to just use IncMillisecond(Trunc(Value) + 1, -1); for Final date, and just simply Trunc(Value) for Start date.
    I also don’t like the way you mixed in a fixed date format and the fixed SQL statement. Those are jobs of the presentation layer and the persistence layer. Besides that, I use records like that a lot 🙂

    Reply
    • Andreano Lanusse
      Andreano Lanusse says:

      Hi Istvan and Louis, thanks for update, my mistake to set to 1000. I updated the post and source code to reflect that.

      About the use of IncMillisecond and Trunc it works, but personally I don’t like that, the code became not intuitive and could generate some confusion, but this is my personal opinion.

      Reply
  3. LDS
    LDS says:

    Don’t generate SQL predicates that way. Use parameters. For example Oracle won’t reuse statements from its cache, and you’ll get hard parse every time (unless you force it to reuse statements but that’s just a stopgap) and fill the cache.

    When will Delphi have a timestamp type that can handle time zones as well?

    Reply
  4. Louis Kleiman
    Louis Kleiman says:

    So, by design, you are missing values in the last second of the year? Dates are floating point. Trying to perform a query that gets everything up to midnight is easy: …where DateColumn >= Trunc(:StartDate) and DateColumn < Trunc(:EndDate) + 1. Plugging a StartDate of 1/1/2010 and an EndDate of 12/31/2010 into this query gives you everything that happened in that year without missing a record. Guaranteed. The Trunc and addition (+1) can be done in the front end or the back end. Your choice.

    Reply
  5. Anthony Frazier
    Anthony Frazier says:

    For reports like that, I’ll pass my InitialDate & FinalDate parameters through StartOfTheDay() and EndOfTheDay() from DateUtils before fetching data.

    With your TDateInterval class, wouldn’t you want SetFinalDate() to use 999 for the msec instead of 1000?

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.