pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalculating relative time/distance from CURRENT_TIMESTAMP
6+ messages / 4 participants
[nested] [flat]
* Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-04 07:54 Gnanakumar <gnanam@zoniac.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gnanakumar @ 2011-08-04 07:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-04 11:03 Amitabh Kant <amitabhkant@gmail.com>
parent: Gnanakumar <gnanam@zoniac.com>
0 siblings, 1 reply; 6+ messages in thread
From: Amitabh Kant @ 2011-08-04 11:03 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-04 11:42 Gnanakumar <gnanam@zoniac.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Gnanakumar @ 2011-08-04 11:42 UTC (permalink / raw)
To: 'Amitabh Kant' <amitabhkant@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-04 21:54 Charlie <scorpdaddy@hotmail.com>
parent: Gnanakumar <gnanam@zoniac.com>
1 sibling, 0 replies; 6+ messages in thread
From: Charlie @ 2011-08-04 21:54 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-sql
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
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
=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-04 22:24 Samuel Gendler <sgendler@ideasculptor.com>
parent: Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 6+ messages in thread
From: Samuel Gendler @ 2011-08-04 22:24 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: Amitabh Kant <amitabhkant@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calculating relative time/distance from CURRENT_TIMESTAMP
@ 2011-08-05 09:07 Gnanakumar <gnanam@zoniac.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 6+ messages in thread
From: Gnanakumar @ 2011-08-05 09:07 UTC (permalink / raw)
To: 'Samuel Gendler' <sgendler@ideasculptor.com>; +Cc: 'Amitabh Kant' <amitabhkant@gmail.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-08-05 09:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-04 07:54 Calculating relative time/distance from CURRENT_TIMESTAMP Gnanakumar <gnanam@zoniac.com>
2011-08-04 11:03 ` Amitabh Kant <amitabhkant@gmail.com>
2011-08-04 11:42 ` Gnanakumar <gnanam@zoniac.com>
2011-08-04 21:54 ` Charlie <scorpdaddy@hotmail.com>
2011-08-04 22:24 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-08-05 09:07 ` Gnanakumar <gnanam@zoniac.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox