Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Posts Tagged ‘oracle pl’

“Pretty” Date Format Function

Posted by Tyler Muth on June 26, 2009

I’m not sure who originally wrote this function (probably Tom Kyte), but I needed it yesterday and couldn’t find it. Thankfully Chris Beck tracked it down in some other code I wrote.  I made a few enhancements, but the bulk of the function is the same.  The output is almost identical to the “SINCE” date format available in Application Express.  So, if you pass in a date in the past to this function, the result will be similar to “25 seconds ago”, “1 minute ago”, “5 months ago”, etc.

Function

create or replace function date_text_format (p_date in date)
return varchar2
as
	x	varchar2(255);
begin
	x := 	case
				when sysdate-p_date < 1/1440
					then round(24*60*60*(sysdate-p_date)) || ' seconds'
				when sysdate-p_date < 1/24
					then round(24*60*(sysdate-p_date)) || ' minutes'
				when sysdate-p_date < 1
					then round(24*(sysdate-p_date)) || ' hours'
				when sysdate-p_date < 14
					then trunc(sysdate-p_date) || ' days'
				$IF $$BRITISH $THEN
					when mod(trunc(sysdate-p_date),14) = 0
						then trunc(sysdate-p_date) / 14 || ' fortnights'
				$END
				when sysdate-p_date < 60
					then trunc((sysdate-p_date)/7) || ' weeks'
				when sysdate-p_date < 365
					then round(months_between(sysdate,p_date)) || ' months'
				else round(months_between(sysdate,p_date)/12,1) || ' years'
		   end;
	x:= regexp_replace(x,'(^1 &#91;&#91;:alnum:&#93;&#93;{4,10})s','\1');
	x:= x || ' ago';
	return x;
end date_text_format;
/&#91;/sourcecode&#93;
<h3>Examples</h3>
select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago
Advertisements

Posted in Oracle, PLSQL, Uncategorized | Tagged: | 12 Comments »