Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

“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

12 Responses to ““Pretty” Date Format Function”

  1. Thanks for sharing!🙂

  2. VA said

    Isn’t this the same as the apex_util.get_since function?

  3. Tyler Muth said

    VA,

    Once again I learn something new from the community. apex_util.get_since was added in 2004 when I was still on the team! This is probably still useful to developers not using APEX, but those are hard to find these days😉 Someone might want to customize this one a bit as well. I suspect apex_util.get_since has been translated into any language that APEX is translated into, which gives it a HUGE advantage over my (really Tom’s) function is you need to support multiple languages.

    Tyler

  4. Lev said

    Thanks for sharing, but what if p_date is in future?

    I think sysdate-p_date should be abs(sysdate-p_date)
    and line 26 should be changed also.

    By the way:
    apex_util.get_since(sysdate + 5) returns 0 seconds.

    Is is a bug or a feature?

    Thanks,
    Lev

  5. FYI: related thread here which is where I cribbed and modified similar code for our product:

    http://oraclequirks.blogspot.com/2008/01/extending-supported-languages-for-since.html

    (note bug fix in comments to post)

  6. […] If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function. […]

  7. Matt said

    Shouldn’t line 25 be x:= regexp_replace(x,'(^1 [[:alnum:]]{3,10})s’,’\1′); – otherwise you end up with “1 days ago”

    Also, you need to embed the $IF $$BRITISH bit as a separate CASE statement within the week check – otherwise SYSDATE – 364 returns “26 fortnights ago” rather than “11 months ago”

  8. Tim Armitage said

    Being English I think I should point out that the use of fortnight isn’t correct. We might refer to a period 2 weeks back as “a fortnight ago” but I’ve never heard anyone refer to multiple units of fortnight – so you would never se “1 fortnight ago” or “3 fortnights ago”.

    Tim

  9. Christoph said

    I hadn’t seen the conditional compilation syntax before. After checking the Oracle docs I found references to the syntax, but could not find anything on $$BRITISH. Could someone please let me know where I can find some documentation on this?

    Thanks,
    C

  10. Amane Matsumoto said

    I am Amane Matsumoto. I am from Japan.
    I am makie-shi (Japanese lacquer artist) and shoka(Japanese calligrapher).
    I have spent my life working on traditional Japanese lacquer(urushi).
    I am the Kyoto style artist and I have samurai soul.
    I can make samurai maetate of Japanese helmets ,inro ,saya of Japanese sword ,gold Japanese lacquer boxes and so on.I have the highest skill in Japan.

    Japanese lacquer art is made with gold ,silver ,platinum and so on.
    The subjects I pick are very often nature.

    I admire Mr.Lawrence Joseph Ellison in every way.
    I am very happy that he research and learn
    about Japanese culture and arts like Zen.I think he is a true samurai.

    I’d like to ask him to become my patron.
    He must have been extra busy , but if he would not mind helping me get through this tough competition, that would be tremendously appreciated.
    I sent my work(sho) and the photograph of my work(urushi)to Oracle head office. But I hardly ever get back his reply.
    It may be that he doesn’t get my photos and works.

    Could you help me?
    I’m at a loss. But I can not want to give up.
    I would like to contact him.
    I would like to go to America to meet him.
    I’d like to ask him to become my patron.
    I wish I could contact him somehow or other.
    Could you help me? My Email Address is spkv9xz9@yahoo.co.jp .

    Thank you so much for your kind reply.

  11. […] 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 [[:alnum:]]{4,10})s','1'); x:= x || ' ago'; return x; end date_text_format; / Examples : 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 this is the function is of the ORACLE. for more details please click here […]

  12. Thank you for your great article. I also must say that your layout is a pleasure to view. Keep up the good work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: