Skip to content

Instantly share code, notes, and snippets.

@fictorial
Last active June 21, 2018 13:50
Show Gist options
  • Save fictorial/542b5dfac9238743f95296c797f0b65f to your computer and use it in GitHub Desktop.
Save fictorial/542b5dfac9238743f95296c797f0b65f to your computer and use it in GitHub Desktop.
time difference in words for postgres
create or replace function time_diff_in_words(
a timestamp with time zone,
b timestamp with time zone
) returns text as $$
declare
_age interval;
_suffix text;
_years integer;
_months integer;
_days integer;
_hours integer;
_minutes integer;
_seconds integer;
_year_part text;
_month_part text;
_day_part text;
_hour_part text;
_minute_part text;
_second_part text;
_parts text ARRAY;
begin
_age = age(a,b);
_suffix = case when a > b then ' ago' else ' from now' end;
_years = abs(date_part('year', _age));
_months = abs(date_part('month', _age));
_days = abs(date_part('day', _age));
_hours = abs(date_part('hour', _age));
_minutes = abs(date_part('minute', _age));
_seconds = abs(date_part('second', _age));
_year_part = case when _years = 1 then '1 year' else _years || ' years' end;
_month_part = case when _months = 1 then '1 month' else _months || ' months' end;
_day_part = case when _days = 1 then '1 day' else _days || ' days' end;
_hour_part = case when _hours = 1 then '1 hour' else _hours || ' hours' end;
_minute_part = case when _minutes = 1 then '1 minute' else _minutes || ' minutes' end;
_second_part = case when _seconds = 1 then '1 second' else _seconds || ' seconds' end;
if _years > 0 then _parts = array_append(_parts, _year_part); end if;
if _months > 0 then _parts = array_append(_parts, _month_part); end if;
if _days > 0 then _parts = array_append(_parts, _day_part); end if;
if _hours > 0 then _parts = array_append(_parts, _hour_part); end if;
if _minutes > 0 then _parts = array_append(_parts, _minute_part); end if;
if _seconds > 0 then _parts = array_append(_parts, _second_part); end if;
return array_to_string(_parts, ', ') || _suffix;
end;
$$ language plpgsql volatile;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment