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 [[: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
I watched the Discovery Science series “Moon Machines” a few weeks ago, as I’m pretty much obsessed with all things space or flying. In the “Navigation Computer” episode, there was a section about the memory they used for the Apollo machines that I found fascinating. They actually sent their programs off to a factory to have them woven into a “rope” of 1’s and 0’s!!! This makes punch-cards look convenient.
Check out the video on YouTube from about 3:00 to 6:00 min:
I have a great opportunity to speak to a group of University students about blogging. I have a lot of advice based on my experiences, but I discovered early on that one of the most valuable aspects of blogging is that you can learn more from your community than they can learn from you (if you are willing to listen). So, what advice would you offer someone who is considering starting a blog? These students are undergraduates in the Business School and the title of the course is “Social Networking and Business”.
The irony of this question is that one piece of advice I have for them is to post often, yet I’ve been slacking lately. It’s not because I don’t want to blog, I really enjoy it, but I’ve had some pretty big “projects” going on lately and something had to give. More on that in another post…
I’m working on a publication and one concept I’d like to include is the use of mod_rewrite and APEX as it helps simplify another concept. Here’s the problem, while I’ve used mod_rewrite in many production environments, I’ve never used the exact concept I want to write about. Essentially, I want to force all requests for the APEX development environment through one Database Access Descriptor (DAD), and all requests for other applications to a different DAD.
I’ve written and tested the rules for this, but I’m asking for some help from the community to check my work and offer suggestions. Sorry, I’m not offering money, or even recognition in the publication (it’s something I cant commit to), but I will definitely acknowledge it here if someone catches an error of mine or makes a suggestion that I use.
So, my goal is to force all requests for “apex”, “apex_admin”, and f?p=4xxx to the “builder” DAD, while forcing all requests to f?p= (anything other than 4000-4999) to the “apex” DAD.
RewriteEngine on
Options +FollowSymLinks
#RewriteLog "/tmp/rewrite.log"
#RewriteLogLevel 3
# Change all requests for "apex" shortcuts to the builder DAD
RewriteRule /pls/apex/apex /pls/builder/apex [R]
RewriteRule /pls/apex/apex_admin /pls/builder/apex_admin [R]
# Change all 4000s to builder DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/apex/f?p=(4[0-9]{3}:.*)
RewriteRule /pls/apex/ /pls/builder/f?p=%1 [R,L]
# Change 4, 40-49, 400-499, or any number starting with 4 greater than 40000 to apex DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/builder/f?p=((4[0-9]{0,2}:)|(4[0-9]{4,}:))
RewriteRule /pls/builder/ /pls/apex/f?p=%1 [R]
# Change all numbers not starting with 4 to apex DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/builder/f?p=(([1-3]|[5-9])[0-9]*:.*)
By “Here” I mean the Washington, DC Metropolitan area. I live in Bethesda, Maryland. The Oracle office is in Reston, Virginia, exactly 17.8 miles from my house. 95% of the trip is on large, 8 lane highways, so of course google maps estimates this will take 26 minutes. Whatever. I went it into the office twice this week and each time it took me over an hour to get home… 1:04 to be exact on Friday… and I left at 4pm!!! The traffic here is hard to believe, and Friday was sunny. If it rains or snows, forget it, I’m not leaving my house. If my management wasn’t flexible about working from home I’d either have to find a closer house or new job… and I happen to like both.
The other thing is the heat. 99°F and what felt like 99% humidity is just miserable in the summer. OK, I’m done complaining. There are a lot of great things about this area too, I just can’t remember them right now.
Anyone who answers a lot of questions on the Oracle OTN Forums knows that a lot of the answers are just links to Documentation, Blog posts, or other Forum posts. There are 3 tools that I use on a regular basis to make posting links easier.
Firefox “CoLT” (Copy Link Text) Add-on
CoLT is a great add-on that adds a number of options to the right-click menu, allowing you to copy the link title and URL as the exact BB Code syntax that you need to paste into the OTN Forums.
Firefox “Web Developer Toolbar” Add-on
Web Developer adds a lot of great features for, well, web developers, but it also has a killer feature for linking. Under the “Information” section there is the option to “Display Anchors”. Many web pages, especially Oracle Documentation, include a hidden anchor tag for each sub-section. This extension exposes all of these anchors allowing you to easily link directly to a particular section.
A Clipboard Extension or Manager
I use Ditto on Windows as it allows me to copy several links at a time then recall them and paste them into a forum post instead of switching back and forth for each link. It also allows you to store popular text (links in this case) that you use often. I’m sure there are comparable tools for Mac and Linux as well.
If you haven’t heard, today is the day to download Firefox 3 and help set a world record:
Download Day is here! Set a Guinness World Record Enjoy a Better Web
Sounds like a good deal, right? All you have to do to help us set the record for the most software downloaded in 24 hours is get Firefox 3 now – it’s that easy. We’re not asking you to swallow a sword or to balance 30 spoons on your face, although that would be kind of awesome.
Please download Firefox 3 by 17:00 UTC on June 18, 2008. That’s 10:00 a.m. in Mountain View, 1:00 p.m. in Toronto, 2:00 p.m. in Rio de Janeiro, 7:00 p.m. in Paris, Madrid, Berlin, Rome and Warsaw, 9:00 p.m. in Moscow, and June 19, 2008 at 1:00 a.m. in Beijing and 2:00 a.m. in Tokyo.
For those who haven’t heard, orablogs.com is gone and you really do NOT want to go there as there’s some very NSFW links now. I’ve seen a number of posts from people who lost old content hosted there who wish to retrieve or view it. Way Back Machine to the rescue! Here’s a direct link to the pages that the Way Back Machine has archived for orablogs.com.
Earlier this week I was chatting with Tom Kyte over IM about blog traffic. I was just curious about his stats as I wanted to set some goals for myself. Yes, I know we are in a completely different league, but hey, I guy can dream, right? The conversation paused for a minute, then Tom replied with “tell me what your stats are like tomorrow”. During that pause he wrote this simple post. For those of you familiar with the Digg effect, or the phenomenon formerly know as the Slashdot effect, Tom had a similar impact on my traffic, at least in the context of the Oracle community. My traffic spiked to 3x my best day. I’ve had more hits this week than any previous month!
Why do I care? AdSense baby! Just kidding. My primary goal is motivation. Knowing that people read my posts motivates me to write more. If my average daily hits drop below, say 2, I will probably stop blogging. With this huge surge in traffic, I feel an obligation to post something useful and to do it soon (though this post should not count as useful). I’ll think of something by the weekend…
Blogging has been a goal of mine for a long time. As a daily consumer of many great blogs, I’ve felt the need to start giving back at some point, but never knew where to start. I was always looking for that killer topic for my first post, but never found it. My goal was not just to contribute, but to become more involved in “the community”. Tom Kyte has been a big proponent of community and of all the things I’ve learned from him, this is probably the most important. When I was on the APEX development team, I spent a lot of time answering questions on the APEX forum. My goal was to help others learn about APEX, but I have to admit that I learned more from that community than anyone on that forum ever learned from me. There were a lot of challenging questions, requiring a lot of thought, research, and trial-and-error development. There were also a lot of creative alternatives to my solutions posted by other members of that community.
Now that I’ve got the introduction out of the way, it’s time to start posting something worth reading…
