Monthly Archives: March 2007

Storing Time Information In Oracle

Database, Programming

I recently had a requirement to store a time within Oracle, not a timestamp or a date – just the time. As it turns out, storing just the time information in Oracle isn’t something you can do without a little bit of work.

Oracle provides you a few different data types for handling date and time:

  • DATE
  • TIMESTAMP
  • INTERVAL

Unfortunately, none of the supplied data types are an exact match for a time such as 10:30 AM. The good news is that both a DATE and a TIMESTAMP data types contain time information – so it is possible to do what you want with a little slight of hand.

The slight of hand I mentioned has to do with the default behaviour of Oracle when inserting only a time component of a DATE or a TIMESTAMP data type. For the rest of this discussion, lets assume you have a simple table set up as follows:

  1. CREATE TABLE Times (
  2. id INT NOT NULL,
  3. thedate DATE NOT NULL,
  4. thetimestamp TIMESTAMP NOT NULL
  5. );

With the following three rows of data:

  1. INSERT INTO Times (id, thedate, thetimestamp) VALUES (1, SYSDATE, SYSDATE);
  2. INSERT INTO Times (id, thedate, thetimestamp) VALUES (2, TO_DATE('10:30 AM', 'HH:MI AM'), TO_DATE('10:30 AM', 'HH:MI AM'));
  3. INSERT INTO Times (id, thedate, thetimestamp) VALUES (3, TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'), TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'));

When selecting that grid of information out, you’ll receive:

id thedate thetimestamp
1 28/03/2007 11:32:25 PM 28/03/2007 11:32:25.000000 PM
2 1/03/2007 10:30:00 AM 1/03/2007 10:30:00.000000 AM
3 1/01/0001 10:30:00 AM 1/01/0001 10:30:00.000000 AM

There are more than one solution to this problem, as if you’re using any of the standard date/time data types – you can always store the time component. What may or may not be of interest to everyone is what Oracle does with the date component of a DATE or TIMESTAMP data type when you don’t provide the standard date components of a date/time data type.

If you take notice of row #1 returned, you’ll see that because the inserted values for thedate and thetimetamp was SYSDATE, it has stored the date component as you’d expect.

Compare that against row #2 and you’ll notice that the insert statement simply provided the time component and made no mention of the date. When Oracle returned that time value out, it has automatically substituted the first day of the current month into the date component.

When inserting row #3, an arbitrary date of 1 Jan 0001 was supplied. When reading that information back out of Oracle, it handles the date component in a similar fashion to how it was handled for row #1 using the SYSDATE; ie you get the expected result back.

Since you can return any number of rows from Oracle doing standard date/time arithmetic, it really comes down to preference. What option above has the most semantic meaning to a user or the data in the database when you’re required to store a time and not a timestamp?

For consistency reasons, my personal preference leans towards applying technique #3 and here are a couple of simple reasons why:

  • When dealing with a DATE or TIMESTAMP value stored like that in PL/SQL, you’ll always know what value is stored in the date component of the value.
  • If you’re handling this information in an application language such as Microsoft .NET, Java or Python – you will always know what value to expect in the date component. Using technique #1, the date component will change for every row. Using #2, the date component of the value will change per row depending on what month the row was inserted

Does anyone else have a hot tip for storing a straight time value in Oracle?

Search Engine Optimisation & Referral Tracking

Advertising, Search

If you’re looking to set up an affiliate network or you’ve already got one, you should be aware of a couple important points which might just change how you have or are thinking about setting it up.

Physically setting up an affiliate program is quite straight forward and you have two choices in handling the inbound links and referral tracking:

Single entry point
Using a single entry point in your site, where everyone links to with their respective referral code which then shunts the user to the desired page. Using this method, you might end up with:

  • http://mydomain.com/tracking.php?ref=abc&destination=1
  • http://mydomain.com/tracking.php?ref=abc&destination=2
  • http://mydomain.com/tracking.php?ref=abc&destination=3
Multiple entry points
Allowing multiple entry points facilitates deep linking. If you allow multiple entry points, you might end up with:

  • http://mydomain.com/page-1/?ref=abc
  • http://mydomain.com/page-2/?ref=abc
  • http://mydomain.com/page-3/?ref=abc

Both of these methods will work but which one is better for search engine optimisation? If you use a single entry point, you end up in a position where you’ll have hundreds or thousands of inbound links to a particular page. Unfortunately, the page that they are linking to isn’t useful to a search engine for indexing – it simply redirects to another page. You do however get the benefit of being able to effortlessly reorganise a web sites structure and only have to worry about updating destination URL’s in a single location.

Using multiple entry points allows your marketing or affiliates to link directly to their intended page with their referral code, which can make a difference on various levels:

  • it’s convenient for the people linking to the page
  • it’s less error prone, as the linker can simply copy the URL from the browser
  • the linked URL will begin to gain inbound links, which is critical for effective search engine optimisation
  • the person clicking on the URL can hover the URL and see where it is going

The last point might seem like something you might otherwise gloss over, however as internet users become more savvy – they are becoming acutely aware of their online actions. Letting the user clicking on the link see the destination URL will help build trust between your web site and them, as they will be less inclined to think the link is spam.

My personal preference is towards deep linking, it’s just so convenient. If you allow deep linking, the next problem you have is your affiliated links making their way into the search engine result pages; which is definitely not what you want. Fortunately, through the use of a robots.txt file it is possible to drop the affiliated URL’s from being indexed. In the above multiple entry point example, you could stop those URL’s from being indexed by including the following line into your robots.txt file:

  1. User-agent: *
  2. Disallow: /?ref

Unfortunately, your work isn’t quite done, as all of the inbound links are linking into distinct URL’s (ie, with the referral code). As far as a search engine is concerned, these are totally separate web pages which could/should have unique content. To leverage the most out of your inbound links, you want to make sure the link ends up pointing to the permanent URL for the content (ie, without the referral code).

Remembering that you are tracking referral codes, the web site must first do something useful with the referral code. Useful might be placing the referral code in a cookie for later use or storing it in a database, but something generally needs to happen with it. Once the useful action has been completed, you need to send a standard HTTP redirect to the user agent (browser, bot, ..) to tell it the permanent URL for that content exists at a different URL – in this case the same URL without the referral code. Consult the documentation for your favourite server side language about handling HTTP response codes.

By implementing these two simple techniques, you now only have a single copy of any of your web pages indexed in the search engines and any inbound referral links will ultimately be attributed to the permanent URL for the actual content.

You can now sleep easily at night knowing you have search engine optimised referral tracking.

Akismet, Stopping Website Comment Spam The Easy Way

Blogging, Software

Akismet web site spam filtering has blocked 100,021 spam messagesBy now, everyone should be acutely aware that I hate spam. In fact, I hate it that much that I think I’ve written about it every couple of months:

This time around it is no different, as Akismet just keeps on keeping on. I first installed Akismet toward the start of August 2006 as I was being overwhelmed by the volume of comment spam I was receiving. Initially, it was just one or two website spam messages but it soon increased at a rapid pace. I was receiving so much comment spam, I just couldn’t handle it manually anymore and I needed a way of stopping the spam for good.

After looking around at various WordPress comment spam filtering solutions, such as Bad Behaviour and Spam Karma; I ended up deciding on Akismet. There were a couple of reasons that I felt that Akismet was going to perform a better job stopping website spam, the main one being it was driven by the community. Spammers just love to prey on the masses and there sure is a mass of blogs on the internet. If the blogging community backed Akismet, then it seemed reasonable that as soon as enough bloggers flagged something as being spam – I wouldn’t have to worry about it either. As it turns out, this is absolutely the case as very few comment spam messages actually get through the Akismet filtering. I hate web site spam so much that I have comment moderation on as well; I know it might frustrate some commentors but I would prefer to vet a valid comment than see spam land on my site.

Akismet has now been protecting this site from website comment spam for approximately nine months and in that time it has successfully axed about 100,000 spam messages from ever appearing on the site. I can only imagine how hard it must be for people running web sites or forums these days that don’t have easy plugin access to a service like Akismet to stop website spam.

Pretty significant milestone I thought, go Akismet go!

The ConTest Game Show: Slightly Less Crap Than Before

TV

Last month I voiced my opinion about the new Channel Ten game show named The ConTest and noted that I thought it was some what anticlimactic.

A month after my initial review of The ConTest game show and one of the items on my hit list have been addressed. This makes me think that either Channel Ten deliberately launched a crap game show, knowing they were going to make improvements or they’ve been scanning the headlines on the internet and maybe they’ve stumbed across my ever so slightly critical view of their game show; who knows but it is one less thing on the list so its definitely improved the show.

You’re probably wondering which item on the hit list was addressed, well it was the one regarding knowing whether the contestants story was real or not. It’s such a small thing but knowing if they are telling the truth about that helps set the scene for their future habits on the show. The thing I find funny or interesting is to see just how far a contestant will push the truth about their story – some tell the truth, some stretch it a little bit while others try to reinvent themselves for the duration of the show. There also seems to be a reasonable relationship between how far someone pushes the truth regarding their story and what their behaviour is regarding the con.

Now Channel Ten just need to work some sort of a challenging system into their new game show and it’d get really interesting. The obvious implementation involves a contestant being able to optionally challenge an opponent once per round. If they get the challenge correct, they get to keep their opponents money; if they get it wrong they lose all of it.

On the surface, implementing a challenge system into The ConTest is nice in theory and hard in practice as it’ll add other problems which would change how the current game is played. First off, if a contestant bluffs and gets caught out and loses their winnings – how would they bluff during the elimination if the other contestants know they don’t have any money.

One possible solution might be rolling the challenge and elimination into the same event. At the end of a round, the winning contestant can, if they choose, challenge one of their opponents about their winnings. If they get it right, they person caught out is eliminated. If the contestant gets it wrong – the normal elimination takes place.

I’d personally love to see them implement the challenge system into The ConTest but I appreciate that it might be a relatively complex task. However, if they are confident that it’d improve the game – then I’m sure Channel Ten could hype up a new feature which would let them change the rules a little.

Either way, I’m glad to see the small slice of information about the truth behind each contestants story being presented; it definitely improves the game I think.

Snail Mail Spam

Life, Photos

A weeks worth of paper spam delivered weighs in at 1.3 kilogramsAnyone that runs their eyes over this site knows just how much I hate filthy filthy spammers.

This evening I collected our snail mail from the letter box and was confronted with an enormous amount of plain old fashioned junk mail. What I couldn’t believe was that it was so full, I actually struggled to get it out of the letter box; having to brute force the bulk of it out because it was stuffed in so tight.

I’m all for a company advertising their products and making a dollar or four but at some point these big businesses just have to stop and think about what they are doing. In a single week, I received a whopping 1.3 kilograms of spam mail in my letter box. It doesn’t matter what way you cut that, its a huge amount of paper that is just going to waste.

Being a little curious, I ran my eye over the other roughly 100 letter boxes to see how many of them were the same. You’ve probably already guessed, the majority of the mail boxes were exactly the same – choc-o-block full of spammy junk mail. What was even more amazing was that nearly all of the letter boxes have an ‘Australia Post Mail Only’ or ‘No Junk Mail’ label on the lids of their mail boxes.

You’d think that if the senate can pass electronic anti-spam laws, that they’d have dealt with the low tech equivalent version first. Nope, instead people are happily flogging our forests for everything they are worth to help send out huge volumes of spam. If the companies were a little smarter about it, you’d think they’d selectively send spam instead of applying blanket tactics. I hate email spam just as much as paper spam but these companies could save some serious dollars if they invested into email marketing instead of the antiquated paper equivalent.

Save a tree, try killing an electron instead – you’ll find they’re a lot tougher.