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

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

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;

-- An interval consists of a number of years, months, days, hours,
-- minutes, and seconds.  The problem of dividing two intervals lies
-- in the fact that "one month" is not a fixed number of days.
-- Therefore, dividing, say, P1M with P1D would not make sense.

CREATE OR REPLACE FUNCTION interval_months_part(INTERVAL)
        RETURNS INTEGER AS $$
        SELECT EXTRACT(years FROM $1)::INTEGER * 12
                + EXTRACT(months FROM $1)::INTEGER
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Years plus months as a whole number of months';

CREATE OR REPLACE FUNCTION interval_seconds_part(INTERVAL)
        RETURNS DOUBLE PRECISION AS $$
        SELECT EXTRACT(days FROM $1) * 24 * 60 * 60
                + EXTRACT(hours FROM $1) * 60 * 60
                + EXTRACT(mins FROM $1) * 60
                + EXTRACT(secs FROM $1);
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Days, hours, minutes, and seconds as seconds';

-- If we can divide exactly, do so.  Otherwise return NULL, unless
-- fudging is requested, in which case all months are implicitly
-- assumed to be 30 days, (except for 12 months, which become 365.25
-- days), by extracting and dividing the epochs.
CREATE OR REPLACE FUNCTION interval_divide(dividend INTERVAL,
                                                divisor INTERVAL,
                                                fudge BOOL DEFAULT false)
        RETURNS DOUBLE PRECISION AS $$
        SELECT CASE WHEN interval_months_part($1) = 0
                                AND interval_months_part($2) = 0
                        THEN interval_seconds_part($1)
                                / interval_seconds_part($2)
                        WHEN interval_seconds_part($1) = 0
                                AND interval_seconds_part($2) = 0
                        THEN CAST(interval_months_part($1)
                                        AS DOUBLE PRECISION)
                                / interval_months_part($2)
                        ELSE CASE WHEN fudge
                                THEN EXTRACT(EPOCH FROM $1)
                                        / EXTRACT(EPOCH FROM $2)
                                ELSE NULL
                                END
                        END;
$$ LANGUAGE SQL IMMUTABLE STRICT;