Consider: SELECT (NOW() - '1-Aug-2011')::text || ' ago.' user_string; yields: "3 days 18:59:24.781174 ago." From: [email protected] To: [email protected] CC: [email protected] 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:[email protected]] Sent: Thursday, August 04, 2011 4:34 PM To: [email protected] Cc: [email protected] 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 <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
