Miscellaneous Postgres Functions
Similar to EXTRACT or DATE_PART, but rather than returning a particular "item" from the interval value, converts the entire interval into the specified units. That is, whereas DATE_PART('seconds', '1 minute 5 seconds'::interval) will return 5, INTERVAL_CONVERT('seconds', '1 minute 5 seconds'::interval) will return 65. Since timestamp - timestamp returns an interval value this allows you to quickly get numeric answers to questions like "how many days are there between these two dates?", or "how many milliseconds passed between these two log entries?"
- The units accepted for the first parameter are the same as the fields accepted by DATE_TRUNC, except that 'quarter' is not supported.
- A month is considered to be 30 days, and a year 365.25 days.
- The result is returned in floating point format; use appropriate rounding and casting if you require an integer.
CREATE FUNCTION interval_convert(in_unit text, in_interval interval) RETURNS double precision AS $FUNC$ SELECT EXTRACT( EPOCH FROM $2 -- in_interval ) / -- Slightly lazy way of allowing both singular and plural -- has side effect that 'centurie' and 'centurys' are accepted -- but otherwise behaves similarly to DATE_TRUNC CASE TRIM(TRAILING 's' FROM LOWER( $1 -- in_unit )) WHEN 'microsecond' THEN 0.000001 WHEN 'millisecond' THEN 0.001 WHEN 'second' THEN 1 WHEN 'minute' THEN 60 WHEN 'hour' THEN 3600 WHEN 'day' THEN 86400 WHEN 'week' THEN 604800 WHEN 'month' THEN 2592000 -- 30 days -- WHEN 'quarter' THEN -- Not supported WHEN 'year' THEN 31557600 -- 365.35 days WHEN 'decade' THEN 315576000 WHEN 'century' THEN 3155760000 WHEN 'centurie' THEN 3155760000 WHEN 'millennium' THEN 31557600000 WHEN 'millennia' THEN 31557600000 END $FUNC$ LANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT;
Licensing and Contact Info
© Copyright Rowan Collins, 2011, but hereby licensed for use by whomever, for whatever purpose, with no limitation, and accepting no liability, expressed or otherwise.
Let me know what you think of it, and what you're doing with it… Mail me on pg-atom [[AAHTT]] rwec.co.uk