Thread: Calculating relative time/distance from CURRENT_TIMESTAMP

Calculating relative time/distance from CURRENT_TIMESTAMP

From
"Gnanakumar"
Date:
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




Re: Calculating relative time/distance from CURRENT_TIMESTAMP

From
Amitabh Kant
Date:
Have you looked into the date time functions already ?<br /><br /><a
href="http://www.postgresql.org/docs/9.0/static/functions-datetime.html">http://www.postgresql.org/docs/9.0/static/functions-datetime.html</a><br
/><br/><br />Amitabh Kant<br /><br /><div class="gmail_quote">On Thu, Aug 4, 2011 at 1:24 PM, Gnanakumar <span
dir="ltr"><<ahref="mailto:gnanam@zoniac.com">gnanam@zoniac.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> Hi,<br /><br /> Is there any
built-infunction/add-on module available in PostgreSQL, that<br /> converts a given "timestamp"/"timestamptz" value
intoits relative distance<br /> from CURRENT_TIMESTAMP?  For example, relative distance as today, yesterday,<br /> 5
minutesago, 1 week ago, etc.<br /><br /> Regards,<br /> Gnanam<br /><font color="#888888"><br /><br /><br /> --<br />
Sentvia pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make
changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /> 

Re: Calculating relative time/distance from CURRENT_TIMESTAMP

From
"Gnanakumar"
Date:

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

 

Re: Calculating relative time/distance from CURRENT_TIMESTAMP

From
Charlie
Date:
Consider:
 
SELECT (NOW() - '1-Aug-2011')::text || ' ago.' user_string;

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

 

From: gnanam@zoniac.com
To: amitabhkant@gmail.com
CC: pgsql-sql@postgresql.org
Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
Date: Thu, 4 Aug 2011 17:12:51 +0530

.ExternalClass p.ecxMsoNormal, .ExternalClass li.ecxMsoNormal, .ExternalClass div.ecxMsoNormal {margin-bottom:.0001pt;font-size:12.0pt;font-family:'Times New Roman','serif';} .ExternalClass a:link, .ExternalClass span.ecxMsoHyperlink {color:blue;text-decoration:underline;} .ExternalClass a:visited, .ExternalClass span.ecxMsoHyperlinkFollowed {color:purple;text-decoration:underline;} .ExternalClass span.ecxEmailStyle17 {font-family:'Arial','sans-serif';color:blue;font-weight:normal;font-style:normal;} .ExternalClass .ecxMsoChpDefault {;} @page Section1 {size:8.5in 11.0in;} .ExternalClass div.ecxSection1 {page:Section1;}

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

 

Re: Calculating relative time/distance from CURRENT_TIMESTAMP

From
Samuel Gendler
Date:


On Thu, Aug 4, 2011 at 4:42 AM, Gnanakumar <gnanam@zoniac.com> wrote:

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.


If you really want a flexible function for doing this, I suspect you could replicate the functionality of jquery's 'timeago' module in a stored proc relatively easily. http://timeago.yarp.com/ It is MIT licensed, so you can copy the logic without restriction.  It makes reference to being derivative of a ruby project, so you could probably grab that code and convert it to ruby-pg very easily.
 

Re: Calculating relative time/distance from CURRENT_TIMESTAMP

From
"Gnanakumar"
Date:
> If you really want a flexible function for doing this, I suspect you could
replicate the functionality of jquery's 'timeago' module in a stored proc
relatively easily. http://timeago.yarp.com/ It is MIT licensed, so you can
copy the logic without restriction.  It makes reference to being derivative
of a ruby project, so you could probably grab that code and convert it to
ruby-pg very easily.
 
Thanks for that useful link.  I believe in my case this will work out.
Thanks again.