Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save chrisfarms/b65d0b86f05c88d1dbc331ed83133e1d to your computer and use it in GitHub Desktop.
Save chrisfarms/b65d0b86f05c88d1dbc331ed83133e1d to your computer and use it in GitHub Desktop.
Postgres time_ago_in_words(date) function
CREATE OR REPLACE FUNCTION time_ago_in_words(timestamp with time zone)
RETURNS text
LANGUAGE SQL
AS $$
SELECT CASE
WHEN date_part('year', age(current_timestamp, $1)) = 1 THEN concat(date_part('year', age(current_timestamp, $1)), ' year ago')
WHEN date_part('year', age(current_timestamp, $1)) > 1 THEN concat(date_part('year', age(current_timestamp, $1)), ' years ago')
WHEN date_part('month', age(current_timestamp, $1)) = 1 THEN concat(date_part('month', age(current_timestamp, $1)), ' month ago')
WHEN date_part('month', age(current_timestamp, $1)) > 1 THEN concat(date_part('month', age(current_timestamp, $1)), ' months ago')
WHEN date_part('day', age(current_timestamp, $1)) = 1 THEN concat(date_part('day', age(current_timestamp, $1)), ' day ago')
WHEN date_part('day', age(current_timestamp, $1)) > 1 THEN concat(date_part('day', age(current_timestamp, $1)), ' days ago')
WHEN date_part('hour', age(current_timestamp, $1)) = 1 THEN concat(date_part('hour', age(current_timestamp, $1)), ' hour ago')
WHEN date_part('hour', age(current_timestamp, $1)) > 1 THEN concat(date_part('hour', age(current_timestamp, $1)), ' hours ago')
WHEN date_part('minute', age(current_timestamp, $1)) <= 1 THEN concat(date_part('minute', age(current_timestamp, $1)), ' minute ago')
WHEN date_part('minute', age(current_timestamp, $1)) > 1 THEN concat(date_part('minute', age(current_timestamp, $1)), ' minutes ago')
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment