PostgreSQL interval division This has come up a couple of times, e.g., in the postgresql newsgroup and the wiki. In general, the relationship between different intervals may not be well defined - a month can be different numbers of days depending upon which month (and year) is under consideration. But sometimes it is necessary to calculate how many intervals occur between two points in time, e.g. (simplified example):

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;


This isn't allowed in PostgreSQL because it is division between two intervals, which wouldn't have well defined general behaviour for the above reasons. A workaround exists for when an interval can be converted to seconds, but what is the best way when this is not the case, e.g, when the interval is of the order of milliseconds?
As @a_horse_with_no_name mentions in his comment, division can be achieved by conversion to milliseconds.

SELECT 
  (1000*EXTRACT(EPOCH FROM tend - tstart) 
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength) 
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;