postgresql get difference between two dates
select age('2010-04-01', '2012-03-05'),
date_part('year',age('2010-04-01', '2012-03-05')),
date_part('month',age('2010-04-01', '2012-03-05')),
date_part('day',age('2010-04-01', '2012-03-05'));DATEDIFF minute postgres
-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in minutes
SELECT (DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 * 60 +
DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp);
-- Result: 1
-- Time only
SELECT DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
DATE_PART('minute', '08:56:10'::time - '08:54:55'::time);
-- Result: 1postgres time difference in minutes
CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIME, end_t TIME)
RETURNS INT AS $$
DECLARE
diff_interval INTERVAL;
diff INT = 0;
BEGIN
-- Minus operator for TIME returns interval 'HH:MI:SS'
diff_interval = end_t - start_t;
diff = DATE_PART('hour', diff_interval);
IF units IN ('hh', 'hour') THEN
RETURN diff;
END IF;
diff = diff * 60 + DATE_PART('minute', diff_interval);
IF units IN ('mi', 'n', 'minute') THEN
RETURN diff;
END IF;
diff = diff * 60 + DATE_PART('second', diff_interval);
RETURN diff;
END;
$$ LANGUAGE plpgsql;Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
