Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 77E42B5DBE6 for ; Thu, 4 Aug 2011 08:49:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 29950-01 for ; Thu, 4 Aug 2011 11:49:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from rs73.luxsci.com (rs73.luxsci.com [64.49.254.135]) by mail.postgresql.org (Postfix) with ESMTP id 180EEB5DBE2 for ; Thu, 4 Aug 2011 08:49:08 -0300 (ADT) Received: from GNANAM (NSG-Static-233.156.72.182.airtel.in [182.72.156.233] (may be forged)) (authenticated bits=0) by rs73.luxsci.com (8.13.8/8.13.8) with ESMTP id p74BmukD010567; Thu, 4 Aug 2011 06:49:05 -0500 Reply-To: From: "Gnanakumar" To: "'Amitabh Kant'" Cc: References: <009301cc527b$c48690b0$4d93b210$@com> In-Reply-To: Subject: Re: Calculating relative time/distance from CURRENT_TIMESTAMP Date: Thu, 4 Aug 2011 17:12:51 +0530 Organization: Zoniac Software Private Limited Message-ID: <001201cc529b$adc7c220$09574660$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01CC52C9.C77FFE20" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcxSljLvBDVP2ueYQt2GGvk7W2elWQAA1d1A Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/16 X-Sequence-Number: 35684 This is a multipart message in MIME format. ------=_NextPart_000_0013_01CC52C9.C77FFE20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@gmail.com] Sent: Thursday, August 04, 2011 4:34 PM To: gnanam@zoniac.com Cc: pgsql-sql@postgresql.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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_NextPart_000_0013_01CC52C9.C77FFE20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@gmail.com]
Sent: Thursday, August 04, 2011 4:34 PM
To: gnanam@zoniac.com
Cc: pgsql-sql@postgresql.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@zoniac.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@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql=

------=_NextPart_000_0013_01CC52C9.C77FFE20--