Datetime Usage Guide

There are a number of places in Launchpad where datetime types are used. There are three main places where datetime types are represented:

• Python code
• in the database as table columns
• SQLObject wrappers for database tables, which act as an adapter between the above two

• TALES fmt:date, fmt:time and fmt:datetime formatters.

Furthermore, there are two main datetime types in use:

• timestamps, which identify a particular point in time
• time deltas, which identify an interval in time

Data Types

Python

We use the standard datetime module to represent time stamps and time deltas -- the datetime.datetime type for timestamps, and the datetime.timedelta type for time deltas.

To make matters a little bit more complicated, there are actually two types of datetime.datetime objects:

1. naïve datetime objects

2. timezone aware datetime objects

While both objects share the same Python type, they can not be compared with each other. Where possible, we use timezone aware datetime objects.

A timezone aware datetime can be created with the following code:

•    1 import datetime
     2 import pytz
     3 
     4 UTC = pytz.timezone('UTC')
     5 dt = datetime.datetime(2005, 1, 1, 8, 0, 0, tzinfo=UTC)
  

The pytz.timezone() function can be used to retrieve tzinfo objects for any of the named Olsen time zones. A datetime value can be converted to another time zone as follows:

•    1 perth_tz = pytz.timezone('Australia/Perth')
     2 perth_time = dt.astimezone(perth_tz)
  

PostgreSQL

In Postgres, the TIMESTAMP WITHOUT TIME ZONE should be used to represent timestamps, and INTERVAL should be used to represent time deltas. All timestamp columns in the database should store the time in UTC.

While Postgres has a TIMESTAMP WITH TIME ZONE type, it should not be used. The difference between the two column types is that the value of a TIMESTAMP WITH TIME ZONE column will be converted to local time when being read, and the reverse occurs when being written. It does not actually store a time zone with the timestamp.

SQLObject

To wrap a timestamp database column, use the canonical.database.datetimecol.UtcDateTimeCol type. To wrap an interval database column, use the sqlobject.IntervalCol type:

•    1 from sqlobject import IntervalCol
     2 from canonical.database.sqlbase import SQLBase
     3 from canonical.database.datetimecol import UtcDateTimeCol
     4 
     5 class TableName(SQLBase):
     6     timestampcol = UtcDateTimeCol(dbname='timestampcol')
     7     intervalcol = IntervalCol(dbname='intervalcol')
  

The UtcDateTimeCol type differs from the standard SQLObject DateTimeCol in the following ways:

1. when you read a value, a time zone aware datetime value will be returned rather than a naïve value.

2. when you write a value, it will be converted to UTC first. This has the side effect of raising an exception if you attempt to write a naïve value to the database.

The IntervalCol type represents database interval values using the Python timedelta type.

Note that the standard DatetimeCol type should not be used for either timestamps or intervals. It does not correctly handle time zones, and is the wrong type to use for intervals.

Page Templates

Inside page templates, use the following TALES formatters to present timestamp objects:

• fmt:date

• fmt:time

• fmt:datetime

• fmt:approximatedate

The preferred method of presenting datetime is:

• <span
    tal:attributes="title context/datecreates/fmt:datetime"
    tal:content="context/datecreated/fmt:approximatedate">
    2005-10-18
  </span>

When in doubt, use this presentation.

If the timestamp has a time zone attached, these formatters will convert the date to the user's local time before display.

For time interval objects, use the following formatters:

• fmt:exactduration

• fmt:approximateduration

Two Concepts of "Now"

When working with the database, there are two distinct concepts of "now" to work with:

1. the time when the code is running (e.g. returned by datetime.now()).

2. the database transaction time (when the transaction is committed, all the changes will appear to have happened atomically at that time).

Usually these two mean almost the same thing, but they will differ under the following conditions:

• clock skew between the application server and database server (should not be a problem on our servers).
• with long running transactions, the second "now" will be the time at the start of the transaction.

In cases where you are comparing timestamps, mixing the two concepts of "now" can result in race conditions. In most cases in Launchpad, the database transaction time is the correct one to use.

Database Transaction Time

Storing the current database transaction time in the database use the following syntax:

•    1 from canonical.database.constants import UTC_NOW
     2 
     3 person.datecreated = UTC_NOW
  

(note that you won't be able to read the value as a Python datetime object until the sync() method for the SQLObject is called, or the transaction is committed).

To store a time relative to the present time in a database column, we can make use of the fact that UTC_NOW is an "sql builder" type:

•    1 membership.dateexpires = UTC_NOW + datetime.timedelta(months=6)
  

The database transaction time can be retrieved like so:

•    1 from canonical.database.sqlbase import cursor
     2 cur = cursor()
     3 cur.execute("SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';")
     4 [database_now] = cur.fetchone()
  

Present Time

To create a Python datetime object that represents the present time, use the following code:

•    1 import datetime
     2 import pytz
     3 
     4 UTC = pytz.timezone('UTC')
     5 dt = datetime.datetime.now(UTC)
  

Note that the datetime.utcnow() method should not be used -- it creates a naïve datetime value, which can not be compared against other values in Launchpad.