Re: Calculating relative time/distance from CURRENT_TIMESTAMP

Consider: SELECT (NOW() - '1-Aug-2011')::text || ' ago.' user_string;
yields: "3 days 18:59:24.781174 ago."
From: gnanam(at)zoniac(dot)com
To: amitabhkant(at)gmail(dot)com
CC: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
Date: Thu, 4 Aug 2011 17:12:51 +0530

Hi Amitabh,

Yes, I already took a glance of the Date/Time functions. But what
I’m expecting is something more meaningful/user-friendly value to be
returned than from the actual return value of these functions available here.
I’m just finding out whether this could be solved at query level itself.

For example, there is age(timestamp, timestamp) function.
If I call like this select
age(current_timestamp, (current_timestamp - interval '1 day')) it returns

“1 day”. But what I’m expecting to be returned
from the function is something more meaningful/user-friendly to the end users.
In this case, I wanted it to return “yesterday”, similarly today, 15
minutes ago, 1 week ago, etc.

Regards,

Gnanam

From: Amitabh Kant
[mailto:amitabhkant(at)gmail(dot)com]

Sent: Thursday, August 04, 2011 4:34 PM

To: gnanam(at)zoniac(dot)com

Cc: pgsql-sql(at)postgresql(dot)org

Subject: Re: [SQL] Calculating relative time/distance from
CURRENT_TIMESTAMP

Have you looked into the date
time functions already ?

http://www.postgresql.org/docs/9.0/static/functions-datetime.html

Amitabh Kant

On Thu, Aug 4, 2011 at 1:24 PM, Gnanakumar <gnanam(at)zoniac(dot)com> wrote:

Hi,

Is there any built-in function/add-on module available in PostgreSQL, that

converts a given "timestamp"/"timestamptz" value into its
relative distance

from CURRENT_TIMESTAMP? For example, relative distance as today,
yesterday,

5 minutes ago, 1 week ago, etc.

Regards,

Gnanam

--

Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-sql