All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).

This makes it easy to display date spans.

To see how old I currently am, I would call:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)

This will return my current age:
30y 114d 38m 11s 620ms

This comes in handy when I have a log table with columns like this:

• process
• timeStart
• timeEnd

Just using DATEDIFF I would have to do this:

SELECT process,
	CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN
		DATEDIFF(s, timeStart, timeEnd)
	ELSE
		''
	END AS [seconds]
FROM logTable

With this function, I can get more informational results just doing this:

SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT)
FROM logTable

The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')

This will return just the year of my current age:
30y

Full code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
		@dateStart DATETIME,
		@dateEnd DATETIME,
		@PRECISION VARCHAR(2) = 'ms'
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @diffAsString VARCHAR(200),
		@dateScratch DATETIME,
		@years INT,
		@days INT,
		@hours INT,
		@minutes INT,
		@seconds INT,
		@milliseconds INT
 
	SET @diffAsString = ''
	IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
		RETURN @diffAsString
	END
 
	-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
	SET @dateScratch = @dateStart
 
	-- years
	SET @years = DATEDIFF(yy, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(yy, @years, @dateScratch)
 
	-- days
	SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(d, @days, @dateScratch)
 
	-- hours
	SET @hours = DATEDIFF(hh, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(hh, @hours, @dateScratch)
 
	-- minutes
	SET @minutes = DATEDIFF(mi, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(mi, @minutes, @dateScratch)
 
	-- seconds
	SET @seconds = DATEDIFF(s, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(s, @seconds, @dateScratch)
 
	-- milliseconds
	SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
 
 
	-- Build the output string based on the precision
 
	-- years
	IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@years > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) +  'y '
		END
	END
 
	-- days
	IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@days > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) +  'd '
		END
	END
 
	-- hours
	IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@hours > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) +  'h '
		END
	END
 
	-- minutes
	IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@minutes > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) +  'm '
		END
	END
 
	-- seconds
	IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@seconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) +  's '
		END
	END
 
	-- milliseconds
	IF (@PRECISION = 'ms') BEGIN
		IF (@milliseconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) +  'ms '
		END
	END
 
	-- the above string concat always ends with a space, if the space is there at the end remove it
	IF (LEN(@diffAsString) > 0) BEGIN
		IF (RIGHT(@diffAsString, 1) = '') BEGIN
			SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString))
		END
	END
	
	RETURN @diffAsString
END
GO

I just spent the night updating all of my DNS servers because of the flaw that was released today. I had thought I could put it off for a few more days and have time to prepare, but that can never be the case.

One of my annoyances with patching Linux, and Bind in particular is that every time I do an upgrade it seems to break something, today is no exception. I patched three fedora core 9 systems, and some version of Cent OS without any problems.

Updating a FC6 system is where the fun came in — I immediately started getting this error on startup:

zone wirelust.com/IN: loading master file /var/named/zones/wirelust.com.dns: file not found
_default/wirelust.com/in: file not found

It took a few hours of googling to find a solution, but it turns out that somewhere between the previous version and this new one (31:9.3.4-8.P1.fc6) it defaults to a chroot configuration..

The fix was to simply copy all of my zone files from:
/var/named/zones/

to the folder:
/var/named/chroot/var/named/zones

Charts are simple to make with JFreeChart and there are tons of examples on the web about how to generate them and save them as .jpg or .gif files. I ran into an issue however, where I needed to embed a chart into a pdf file. My first thoughts were to generate the charts as a .jpg file and embed them, but the pdfs looked a little choppy and didn’t print well.

To solve the problem you need to have the chart rendered as vector graphics and inserted into the pdf. The code required to do this ended up being simple, but I wasn’t able to find any good complete examples on the web. This is a simple working example I made for anyone else who finds themselves in this position.

Downloads:

• Full source / binary / libs
• Java source
• Generated pdf

компютри

To do an ant build, all you need to do is set j2ee.lib.dir in the build.properties file to a directory that contains the j2ee.jar (in your jdk or app server) and you are good to go. The war file should easily deploy on any app server.

Here is the meat of the code required:

docWriter = PdfWriter.getInstance(doc, baosPDF);
 
doc.addProducer();
doc.addCreator(this.getClass().getName());
doc.addTitle("jfreechart pdf");
doc.setPageSize(PageSize.LETTER);
 
doc.open();
 
// add some text to the document
doc.add(new Phrase("Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."));
 
// build up the dataset for the char
XYSeriesCollection dataset = new XYSeriesCollection();
 
XYSeries series = new XYSeries("XYGraph");
series.add(1, 1);
series.add(2, 3);
series.add(3, 9);
series.add(4, 11);
 
dataset.addSeries(series);
 
// set up the chart
JFreeChart chart = ChartFactory.createXYLineChart("XY Chart Sample, non default font", // chart title
        "x-axis",   // domain axis label
        "y-axis",   // range axis label
        dataset,    // data
        PlotOrientation.VERTICAL,   // orientation
        true,   // include legend
        true,   // tooltips
        false   // urls
    );
 
// trick to change the default font of the chart
chart.setTitle(new TextTitle("XY Chart Sample, non default font", new java.awt.Font("Serif", Font.BOLD, 12)));
chart.setBackgroundPaint(Color.white);
chart.setBorderPaint(Color.black);
chart.setBorderStroke(new BasicStroke(1));
chart.setBorderVisible(true);
 
int width = 260;
int height = 250;
 
// get the direct pdf content
PdfContentByte dc = docWriter.getDirectContent();
 
// get a pdf template from the direct content
PdfTemplate tp = dc.createTemplate(width, height);
 
// create an AWT renderer from the pdf template
Graphics2D g2 = tp.createGraphics(width, height, new DefaultFontMapper() );
Rectangle2D r2D = new Rectangle2D.Double(0,0, width,height);
chart.draw(g2,r2D,null);
g2.dispose();
 
// add the rendered pdf template to the direct content
// you will have to play around with this because the chart is absolutely positioned.
// 38 is just a typical left margin
// docWriter.getVerticalPosition(true) will approximate the position that the content above the chart ended
dc.addTemplate(tp, 38, docWriter.getVerticalPosition(true)-height);

If you had any doubt that Dean Kamen and his team were the best engineers out there, check out this video of the Luke Arm. Their earlier demos were pretty neat, but this one really shows off the devices abilities. Hopefully they leave it with that chrome exterior when they release it on the public.

This isn’t exactly technology related, but I felt like it was worth getting out.

There are some basic communication tasks that I continually surprised that business professionals struggle with. The most notable problematic areas I have observed are:

• naming files
• writing email subject lines
• choosing a communication medium
• leaving voicemails

I can’t tell where the issue lies and how to correct it. Perhaps people aren’t taught communication enough in school. Perhaps communication isn’t stressed enough as something you have to learn. Perhaps people are just lazy.

I have a feeling that part of the problem is that not enough people correct someone when they aren’t being clear about something. I used to just ignore bad behavior, but in recent years I have become increasingly more likely to suggest improvements to people. I find that my clients and business associates who are the worst offenders tend to get worse as time goes on unless I speak up.

Now, before I start, I should note that these suggestions are partially just my personal preferences. There is no hard rule to good communication, but here are some ideas that work for me.

Reserved Words

I cringe every time someone sends me a file with a name like “site_layout_final.zip”. This would be fine except that in business there is never such a thing as “final”. When you get a file like this, you know that the next day you are going to get another file named “site_layout_final_FINAL.zip”, and thus begins the fun.

My list of words that should never appear in a file name:

• new
• temp
• final
• draft
• master
• complete
• last
• test

There are probably others I am forgetting. If you ever put one of these in a file name, write this list down on a sticky note on your computer and slap yourself every time you try to use one. Getting these out of your file naming vocabulary will make the world a better place.

Prefixing
Often times when people are sending a lot of files, they will attempt to name them things in order to force operating system sorting. File names like “_configuration.xml”, or “!configuration.xml”.

I don’t think I need to explain too much why this is a bad idea other than to say it assumes an importance of some files over others and assumes that the prefixed files are the most important. This thinking all too often leads to a prefix battle where newer versions become “!!configuration.xml” and “!!!configuration.xml”.

Prefixing files does not add in any way to the description of what is contained in the file, which should always be a goal.

If you do need a grouping of files to sort, you should think about these alternatives:

Are the files part of a set? If so a folder for each set plus a numeric prefix may be appropriate. Just remember to pad numeric file names with 0 to make sure they sort appropriately.

An example of this set naming might be:

• bracelet 01.jpg
• bracelet 02.jpg
• bracelet 03.jpg

or even

• 01 bracelet.jpg
• 02 earring.jpg
• 03 necklace.jpg

What about Dates?
Any file that is relevant to only a single date, or a date span can often be named with the appropriate date in the file name. This makes the file clear from the name what it includes, but also helps greatly with sorting.

You will thank yourself later if you come up with a way of writing dates that is consistent and logical.

Naming one file revenue_01_08.xls and another file revenue_2_2008.xls is all well and good, but your operating system isn’t going to group them together when you have a folder that has hundreds of these files.

I like to use the format: subject_#yyyy##mm##dd#.ext, with the day optional.

The above files would then become revenue_200801.xls and revenue_200802.xls.

The biggest issue with getting other people to go along with this format seems to be putting the date in the front. For some reason, Americans can’t grasp the concept that this really is the best way to write dates. Naming files with #mm##dd##yyyy# is okay, except that once you have several years worth of files together in a folder, they are all grouped by month, and not sorted in order.

As a general rule, it is usually good practice to make everything in the file name read from Generic to Specific. Again, this seems tough for many people to grasp but it is not all that wacky a concept. This is after all, we often name things with people’s last name before their first name.

The file name revenue_quarterly.xls is generally a better name than quarterly_revenue.xls because revenue is the more generic term. When you add revenue_yearly.xls, it will fit right in and all your revenue files will be grouped.

Choosing a file name is a lot like choosing an email subject. It is easy to just bang on the keyboard to name a file, but you should really be aware that your file is going to live on a computer system with thousands of other files. The name should help you or someone else find the file if they are doing a system search and not have to open up 20 files and read to find the correct one.

1. Make sure the name says something about the content.
   The goal is to make the name generic, but not too generic. revenue_affiliate_quarterly_2007_4.xls is a lot better a name than revenue_2007_4.xls. People all too often use a simple throw away name rather than actually putting in thought as to what the file includes.
2. Avoid using information about the reader in the name.
   At least once a week I get someone emailing me a file named something like “for_terrence.doc”. Don’t do this, period.
   This shouldn’t be confused with a document that is about a user. resume_curran_terrence.doc is a perfectly fine filename because the contents of the file are about Terrence Curran.
3. Don’t be afraid have a long file name
   It has been a long time since we had DOS 8 character restrictions on file names. It is perfectly okay to have filenames that are 10, 20, 50, 100 characters. You should try to make the name short and concise, but there is very little reason to abbreviate or leave out information.

Hopefully this post is useful to someone out there. I will probably add to it in the future as I think of more things, just for my own reference.

Sun has announced that the Sun Spots are now fully open source. This means that the API as well as the Squawk VM are now fully open. The Squak VM itself is an interesting project. I will be curious to see how it compares against google’s Dalvik VM on small devices (that is whenever google releases or tells us more about Dalvik).

The press release seems to imply that the Sun Spot hardware will also be open source:

The open source release of the versatile Java technology-based Sun SPOT platform will include hardware, software and the Squawk Virtual Machine.

but this is the only mention I see of hardware in the press release and on the several sites dedicated to Sun Spot. I really hope they expand on this and show the world everything that is included in the OSS releases.

If the hardware is Open Source that could make way for some third parties to make their own implementations, similar to the different implementaitons of Arduino. This would be especially welcome given the crazy high cost of a Sun Spot device ($250 or so per device compared with $20-150 depending on configuration for Arduino). Yea, Yea, I know Sun Spot is way awesomer than Arduino, but if I am going to make a little device to attach to the top of a rocket or the under carriage of my skateboard, you better believe I am going to be looking for the cheapest hardware I can find.

This is something that wasn’t apparent to me for quite a while and seems to trip me up every now and again.

Say you have two tables. ‘Employees’ and ‘Payroll’.

Employees

column	type
username	varchar(200)
email	varchar(200)

Payroll

column	type
username	varchar(200)
paid	datetime
amount	money

Not great tables, but they will get the point across.

If you wanted to select all employees who have never been paid, you would normally do a query like this:

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
)

What trips up this query is if there is a NULL username in Payroll, it will never return any results. In order to make it work, you have to select all the NON NULL usernames from Payroll.

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
	WHERE username IS NOT NULL
)

This behavior seems to be consistent in MySQL 5 and SQL Server 2005, I haven’t tested in Oracle.

Maybe my Macbook is messed up because I seem to get way more weird issues with it than other people I talk to.

Today it started doing this thing where it thinks the number lock and caps lock buttons are reverse what they should be. In order to type normal, I have to have both the lock lights turned on, rather than turned off. Toggling them on or off seems to work normal, it just thinks that a green light means turned off rather than on.

A bit of digging turns up that it seems like I’m not the only one with this problem. A forum post says that resetting the PRAM fixes the issue, but so does resetting the computer without doing anything sometimes so I don’t know.

Of course, as soon as I post about my OSX kernel panics being fixed, there it is again. This definitely has something to do with networking, but it is tough to tell with all the layers.

I am using:

• WiFi (with patched 802.11n firmware)
• Wired Ethernet
• Tunnelblick (OpenVPN)
• Parallels Shared Networking

The crash always seems to occur, but not consistently:

• When switching from WiFi to Ethernet, or the other way around
• When Tunnelblick connection is dropped unexpectedly
• When switching from shared to host only networking in Parallels

I am going to try my best to recreate the issue consistently soon and be done with it for good.

Update (1/30/2008):
Upgrading my version of Parallels to 3.0, build 5582 has fully fixed the problem once and for all. It has now been over 30 days without a Kernel Panic!

I’ve been using Chandler for a few weeks now to try to organize my life.

I tried building Chandler Server from source today. Following the instructions on their site give’s me a build error because it can’t find a copy of Apache Abdera

Some of the error:

/mnt/drive2/chandler/cosmo/cosmo/src/main/java/org/osaf/cosmo/atom/provider/UserTarget.java:[20,46] cannot find symbol
symbol  : class AbstractTarget
location: package org.apache.abdera.protocol.server.impl
 
/mnt/drive2/chandler/cosmo/cosmo/src/main/java/org/osaf/cosmo/atom/provider/UserTarget.java:[29,32] cannot find symbol
symbol: class AbstractTarget
public class UserTarget extends AbstractTarget {
 
/mnt/drive2/chandler/cosmo/cosmo/src/main/java/org/osaf/cosmo/atom/provider/BaseItemTarget.java:[22,46] cannot find symbol
symbol  : class AbstractTarget
location: package org.apache.abdera.protocol.server.impl
 
/mnt/drive2/chandler/cosmo/cosmo/src/main/java/org/osaf/cosmo/atom/provider/BaseItemTarget.java:[25,45] cannot find symbol
symbol: class AbstractTarget
public abstract class BaseItemTarget extends AbstractTarget

After much fiddeling around, and even trying to install Abdera from source, I was able to get it to build by checking out everything related to cosmo and building that.

• svn co http://svn.osafoundation.org/server/cosmo/trunk
• cd trunk/cosmo
• mvn package

ta-da.

I still get some errors with the unit tests, but I don’t know if those are serious or not yet.

Now let’s see if it run’s with Tomcat 6.0