Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 51FABB5DBC1 for ; Thu, 4 Aug 2011 18:54:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 59716-02 for ; Thu, 4 Aug 2011 21:54:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc1-s20.bay0.hotmail.com (bay0-omc1-s20.bay0.hotmail.com [65.54.190.31]) by mail.postgresql.org (Postfix) with ESMTP id 5DA09B5DBD9 for ; Thu, 4 Aug 2011 18:54:08 -0300 (ADT) Received: from BAY160-W62 ([65.54.190.61]) by bay0-omc1-s20.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 4 Aug 2011 14:54:05 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_d6a1efb5-9e3f-4448-bb01-7628b33490be_" X-Originating-IP: [142.166.58.108] From: Charlie To: CC: Subject: Re: Calculating relative time/distance from CURRENT_TIMESTAMP Date: Thu, 4 Aug 2011 21:54:05 +0000 Importance: Normal In-Reply-To: <001201cc529b$adc7c220$09574660$@com> References: <009301cc527b$c48690b0$4d93b210$@com> , <001201cc529b$adc7c220$09574660$@com> MIME-Version: 1.0 X-OriginalArrivalTime: 04 Aug 2011 21:54:05.0081 (UTC) FILETIME=[07599890:01CC52F1] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.685 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FREEMAIL_REPLY=1.929, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.716 X-Spam-Level: X-Archive-Number: 201108/17 X-Sequence-Number: 35685 --_d6a1efb5-9e3f-4448-bb01-7628b33490be_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Consider: SELECT (NOW() - '1-Aug-2011')::text || ' ago.' user_string=3B yields: "3 days 18:59:24.781174 ago."=20 From: gnanam@zoniac.com To: amitabhkant@gmail.com CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAM= P Date: Thu=2C 4 Aug 2011 17:12:51 +0530 Hi Amitabh=2C =20 Yes=2C I already took a glance of the Date/Time functions. But what I=92m expecting is something more meaningful/user-friendly value to be returned than from the actual return value of these functions available her= e.=20 I=92m just finding out whether this could be solved at query level itself. =20 For example=2C there is age(timestamp=2C timestamp) function.=20 If I call like this select age(current_timestamp=2C (current_timestamp - interval '1 day')) it returns =931 day=94. But what I=92m expecting to be returned from the function is something more meaningful/user-friendly to the end use= rs.=20 In this case=2C I wanted it to return =93yesterday=94=2C similarly today=2C= 15 minutes ago=2C 1 week ago=2C etc. =20 Regards=2C Gnanam =20 From: Amitabh Kant [mailto:amitabhkant@gmail.com]=20 Sent: Thursday=2C August 04=2C 2011 4:34 PM To: gnanam@zoniac.com Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP =20 Have you looked into the date time functions already ? http://www.postgresql.org/docs/9.0/static/functions-datetime.html Amitabh Kant On Thu=2C Aug 4=2C 2011 at 1:24 PM=2C Gnanakumar wrote: Hi=2C Is there any built-in function/add-on module available in PostgreSQL=2C tha= t converts a given "timestamp"/"timestamptz" value into its relative distance from CURRENT_TIMESTAMP? For example=2C relative distance as today=2C yesterday=2C 5 minutes ago=2C 1 week ago=2C etc. Regards=2C Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql =20 = --_d6a1efb5-9e3f-4448-bb01-7628b33490be_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Consider:
 =3B
SELECT (NOW() - '1-Aug-2011')::text || ' ago.' use= r_string=3B

yields:
 =3B
"3 days 18:59:24.781174 ago."
=  =3B

 =3B
From: gnanam@zonia= c.com
To: amitabhkant@gmail.com
CC: pgsql-sql@postgresql.org
Subje= ct: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
= Date: Thu=2C 4 Aug 2011 17:12:51 +0530

Hi Amitabh=2C

 =3B

Yes=2C I already took a glance of th= e Date/Time functions. =3B But what I=92m expecting is something more meaningful/user-friendly value to be returned than from the actual return value of these functions available her= e. =3B I=92m just finding out whether this could be solved at query level itself.<= /span>

 =3B

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

=931 day=94. =3B But what I=92m = expecting to be returned from the function is something more meaningful/user-friendly to the end use= rs. =3B In this case=2C I wanted it to return =93yesterday=94=2C similarly today=2C= 15 minutes ago=2C 1 week ago=2C etc.

 =3B

Regards=2C

Gnanam

 =3B

From: Amitabh Kant [mailto:amitabhkant@gmail.com]
Sent: Thursday=2C August 04=2C 2011 4:34 PM
To: gnanam@zoniac.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP

 =3B

Have you looked = into the date time functions already ?

http://www.postgresql.org/docs/9.0/static/functions-da= tetime.html


Amitabh Kant

On Thu=2C Aug 4=2C 2011 at 1:24 PM=2C Gnanakumar = <=3Bgnanam@zoniac.com>=3B wrot= e:

Hi=2C

Is there any built-in function/add-on module available in PostgreSQL=2C tha= t
converts a given "timestamp"/"timestamptz" value into its relative distance
from CURRENT_TIMESTAMP?  =3BFor example=2C relative distance as today= =2C yesterday=2C
5 minutes ago=2C 1 week ago=2C etc.

Regards=2C
Gnanam



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

 =3B

= --_d6a1efb5-9e3f-4448-bb01-7628b33490be_--