HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL interval division

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlintervaldivision

Problem

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?

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#152929, answer score: 3

Revisions (0)

No revisions yet.