Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

April 22, 2004

Date Arithmetic in SQL Object

Date Arithmetic in SQL Object

Reading this thread (alternate source) on the SQL Object mailing list today I wondered how I would write a similar method using dates instead of integers.

Often in a database you will record, for instance, a person's date of birth but want to be able to display their age as well. This looks like a perfect place for a Python descriptor, as described in this how-to.

This is all well and good until you consider the actual date arithmetic. The problem being that Python hasn't, until recently, had a standard date datatype so comparing dates is always a little tricky. This is complicated further when you introduce databases into the mix as they all have their own (slightly different naturally) date datatypes. In essence, with two points in time you could be dealing withfour different interpretations (or data types) one each from the database and Python for either of the dates. Like I said, it can get a little complicated.

The Python standard database API can help us a little here. Although the only requirement for compliance is that the module author provides a Date(year, month, day) method to construct an appropriate date object. The way in which dates are stored is up to the module author. Therefore it isn't possible to take a value returned from one database module and compare it with one returned from another. But we at least have a standard interface to them.

To turn a date of birth into an age we count the number of whole years between the supplied value and today. For which you need to know (in the correct data type) what today is. This is probably best illustrated with an example. Given a SQL object class;

from SQLObject import *
class Person(SQLObject):
    firstName=StringCol(length=20)
    surName=StringCol(length=20)
    dateOfBirth=DateTimeCol()

We want to add a descriptor method to return an 'age' attribute, something like;

    def _get_age(self):
        "Return the person's age in years"
        return (today-self.buyDate)/365

But, of course, we need to define a value for today and make sure the arithmetic returns the correct values.

When you are writing your own database code this isn't an issue, simply use the Date method supplied by your friendly database module to define today and then your arithmetic is on like objects. In SQL Object, however, you don't necessarily know which database module you are using so you can't use it's Date constructor. Although it is perfectly possible to do so if you want. In this solution I'm trying to be as generic as possible, just because I can.

Luckily, Python 2.3 introduced a standard date and time module. So, if we can get our two dates into objects that this module understands then life starts to get easier. For today's date it's quite easy;

        today=datetime.date.today()

What will seal the deal is if we can convert dateOfBirth into a datetime object. My solution is;

        dob=self.dateOfBirth
        bDate=datetime.date(dob.year, dob.month, dob.day)

And yes, the first line is just a shortcut to make the second line look better on a web page. Once we've done this the arithmetic is really rather simple;

        difference=today-bDate
        return difference.days/365

And voila, when you get an instance of Person and refer to it's age attribute our method is executed. For instance;

>>> people=Person.select()
>>> andy=people(1)
>>> andy.firstname
'Andy'
>>> andy.dateOfBirth
<DateTime object for '1968-10-11 00:00:00.00' at 402eb448>
>>> andy.age
35
>>> 

Posted by Andy Todd at April 22, 2004 10:18 AM

Advertisements