patternsqlMinor
PostgreSQL interval division
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
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?
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.