Miscellaneous Postgres Functions

interval_convert

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