About Greg Moore

Founder and owner of Green Mountain Software, a consulting firm based in the Capital District of New York focusing on SQL Server. Lately I've been doing as much programming as I have DBA work so am learning a lot more about C# and VB.Net than I knew a couple of years ago. When I'm not in front of a computer or with my family I'm often out caving or teaching cave rescue skills.

Hours for the week

Like I say, I don’t generally post SQL specific stuff because, well there’s so many blogs out there that do. But what the heck.

Had a problem the other day. I needed to return the hours worked per timerange for a specific employee. And if they worked no hours, return 0.  So basically had to deal with gaps.

There’s lots of solutions out there, this is mine:

Alter procedure GetEmployeeHoursByDate @startdate date, @enddate date , @userID varchar(25)
as

— Usage exec GetEmployeeHoursByDate ‘2018-01-07’, ‘2018-01-13’, ‘gmoore’

— Author: Greg D. Moore
— Date: 2018-02-12
— Version: 1.0

— Get the totals for the days in question

 

 

set NOCOUNT on

— First let’s create simple table that just has the range of dates we want

; WITH daterange AS (
SELECT @startdate AS WorkDate
UNION ALL
SELECT DATEADD(dd, 1, WorkDate)
FROM daterange s
WHERE DATEADD(dd, 1, WorkDate) <= @enddate)

 

select dr.workdate as workdate, coalesce(a.dailyhours,0) as DailyHours from
(
— Here we get the hours worked and sum them up for that person.

select ph.WorkDate, sum(ph.Hours) as DailyHours from ProjectHours ph
where ph.UserID=@userid
and ph.workdate>= @startdate and ph.workdate <= @enddate
group by ph.workdate
) as a
right outer join daterange dr on dr.WorkDate=a.WorkDate — now join our table of dates to our hours and put in 0 for dates we don’t have hours for
order by workdate

GO

There’s probably better ways, but this worked for me. What’s your solution?

The Basics

Last night at our local SQL Server User Group meeting we had the pleasure of Deborah Melkin speaking.  I first met Deborah at our Albany SQL Saturday Event last year. She gave: Back to the Basics: T-SQL 101. Because of the title I couldn’t help but attend. It wasn’t the 101 part by itself that caught my eye. It was the “Back to the Basics”. While geared to beginners, I thought the idea of going back to the basics of something I take for granted was a great idea. She was also a first time speaker, so I’ll admit, I was curious how she would do.

It was well worth my time. While I’d say most of it was review, I was reminded of a thing or two I had forgotten and taught a thing or two.  But also very importantly, she had a great ability to break down the subject into a clearly understandable talk. This is actually harder than many people realize. I’ve heard some brilliant speakers, who simply can’t convey their message, especially on basic items of knowledge, in a way that beginners can understand it.

So, after the talk last summer, I cornered her at the Speaker’s Dinner and insisted she come up with a follow up, a 201 talk if you will. Last night she obliged, with “Beyond the Select”.  What again struck me about it, was other than a great tip in SSMS 17.4 (highlighting a table alias will show you what the base table is), again nothing was really new to me. She talked about UDFs; I’ve attended entire sessions on UDFs. She talked about CTE; I’ve read extensively about them. She discussed windowing functions; we’ve had one of our presenters present on them locally. Similarly with some of the other items she had brought up.

Now, this is NOT a slight at all, but really a compliment. Both as an attendee and as the guy in charge of selecting speakers, it was great to have a broad-reaching topic. Rather than a deep-drive, this was a bit of everything that gave the audience a chance to learn a bit of everything if they hadn’t seen it before (and based on the reactions and feedback I know many learned new stuff) and to compare different methods of doing things.  For example what’s the advantage of a CTE vs. a derived table vs. a temp table.  Well the answer is of course the DBA’s favorite answer, “it depends”.

As a DBA with decades of experience and as an organizer, it’s tempting to have a Bob Ward type talk every month. I enjoyed his talk last month. But, honestly, sometimes we need to go back and review the basics. We’ll probably learn something new or relearn something we had forgotten. And with talks like Deborah’s, we get to see the big picture, which is also very valuable.

So my final thought this week is that in any subject, not only should we be doing the deep dives that extend our knowledge, but we should review our basics. As DBAs, we do a select every day. We take it for granted, but how many people can really tell you clearly the order of operations? Review the basics once in awhile. You may learn something.

And that’s why I selected this topic for this week’s blog.

Debugging – Powerhell (sic)

Generally I don’t plan to talk too much about specific programming problems. There’s too many blogs out there about how to solve particular problems; and most of them are generally good and even better, accurate.

I’m going to bore most of you, so if you want to jump to the summary, that’s fine. I won’t be insulted (heck I won’t even know!)

That said, last night I spent a lot of time debugging a particular PowerShell script I had written for a client.

I want to talk a bit about what was going on and how I approached the problem.

So, first the setup: It’s really rather simple

  1. Log into an SFTP site
  2. Download a zip file
  3. Expand the zip file
  4. Run an SSIS package to import the files in the zip package to a SQL Server

I had written the script several months ago and it’s been in production since then.  It’s worked great except for one detail. When it was put into production, we didn’t yet have the service account to run it under, so I set it up to run as a scheduled task using my account information. Not ideal, but workable for now.

Since then, we received the service account. So, I had made several accounts to move to the service account, but it wasn’t working.  Yesterday was the first time in awhile I had time to work on it so I started looking at it in more detail.

Permissions Perhaps?

So, the first problem I had was, I’d run the scheduled task and nothing would happen. Ok, that’s not entirely accurate. The script would start, but never run to completion. It just sort of sat there. And it certainly was NOT downloading the zip file.

My first thought was it might be a files permission or other security issue. So the first thing I did was write a really simply script.

get-date | out-file “D:\foo\timetest.text”

Then I setup a scheduled task and ran it manually. And sure enough, timetest.txt showed up exactly where it was supposed to.

So much for security in the OS.

out-file to the Rescue!

So, my next step is probably one of the oldest debug techniques ever: I put in statements in the code to write to a file as I hit various steps in the code.  Not quite as cool as a debugger, but hey, it’s simple and it works.

It was simple stuff like:

“Path $SftpPath Set” | out-file ftp_log.txt -append

BTW, if you’re not familiar with Powershell, one of the nice things is in the above statement, it’ll automatically insert the variable $SftpPath into the string it’s printing out.

This started to work like a charm. And then… jackpot!

“Setting Session using $sftpURL and Password: $password and credentials: $Credential” | out-file ftp_log.txt -append

When I ran it, I’d get a line in the log like:

Setting Session using secureftp.example.com and Password: System.Security.SecureString and Credentials: System.Management.Automation.PSCredentials

Not the most informative, but useful. And it’s nice to know that one can’t easily just print out the password!

But, when I ran it as the service I was getting something VERY different.

Setting Session using secureftp.example.com and Password:  and Credentials:

HUGE difference. What the heck was happening to the password and credentials?

That’s when the first lightbulb hit.

Secure credentials in PowerShell

Let’s take a slight side trip here.  We all know (right, of course you do) that we should never store passwords in cleartext in our scripts.  So I did some digging and realized that PowerShell actually has a nice way to handle this. You can pass a plaintext string to a cmdlet and write that out to a file.  Then when you need credentials, you read from the file and it gets parsed and handed to whatever needs it.

$password = get-content $LocalFilePath\cred.txt | ConvertTo-SecureString

Of course first you have to get the password INTO that cred.txt file.

That’s easy!

if (-not (test-path $LocalFilePath\cred.txt))
{
read-host -AsSecureString | ConvertFrom-SecureString | Out-File     $LocalFilePath\cred.txt
}

So, the first time the program is run, if the cred.txt file isn’t found, the user is prompted for it, they enter the password, it gets put into a secure string and written out. From then on, the cred.txt file is used and the password is nice and secure.

And I confirmed that the service account could actually see the cred.txt file.

So what was happening?

The Key!

It took me a few minutes and when I hit a problem like this I do what I often do, step away from the keyboard. I had to stop and think for a bit. Then it dawned on me. “How does Powershell know how to encrypt and decrypt the password in a secure fashion. I mean at SOME point it needs to have a clear-text version of to pass to the various cmdlets. It wouldn’t be very secure if just anyone could decrypt it.  That’s when it struck me! The encryption key (and decryption key) has to be based on the USER running the cmdlet!

Let me show some more code

$password = get-content $LocalFilePath\cred.txt | ConvertTo-SecureString
$Credential = New-Object System.Management.Automation.PSCredential (‘ftp_example’, $Password)

$sftpURL = ‘secureftp.example.com’

$session = New-SFTPSession -ComputerName $sftpURL -Credential $Credential

So first I’d get the password out of my cred.txt file and then create a Credential Object with a username (ftp_example) and the aforementioned password.

Then I’d try to open a session on the SFTP server using that credential.  And that’s exactly where it was hanging when I ran this under the service account. Now I was on to something. Obviously the $password and $Credential wasn’t getting set as we saw from my debug statements.  It wasn’t able to decrypt cred.txt!

Great, now I just need to have the service create its own cred.txt.  But, I can’t use the same technique where the first time the service runs it prompts the user for the password.  This bothers me. I still don’t have a perfect solution.

For now I fell back on:

if (-not (test-path $LocalFilePath\cred_$env:UserName.txt))
{
# after running once, remove password here!
“password_here” | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File $LocalFilePath\cred_$env:UserName.txt
}

Note I changed the name of the cred.txt file to include the username, so I could have one when I ran it and another when the service account ran it. This makes testing far easier AND solves conflicts when debugging (i.e. “which credentials are currently being used).

So for now the documentation will be “after running the first time, remove the password”. I think more likely I’ll write one-time script that runs to setup a few things and then deletes itself. We’ll see.

Anyway, this worked much better. Now my debug lines were getting the password and all. BUT… things were STILL hanging.  Talk about frustrating. I finally tracked it down to the line

$session = New-SFTPSession -ComputerName $sftpURL -Credential $Credential

Caving Blind

As you know, I love caving. And for caving, a headlamp is a critical piece of equipment. Without it you have no idea where you’re going.  That’s how I felt here. Things were simply hanging and I was getting NO feedback.

I tried

$session = New-SFTPSession -ComputerName $sftpURL -Credential $Credential | out-file ftp_log.txt -append

But I still wasn’t getting anything. I tried a few other things, including setting parameters on how New-SFTPSession would handle errors and warnings. But still nothing. It was like caving blind. The cmdlet was being called, but I couldn’t see ANY errors.  Of course when I ran it as myself, it ran fine. But when I ran it as a service, it simply hung.

I was getting frustrated I couldn’t get any feedback. I figured once I had feedback, the rest would be simple. I was mostly right.

I needed my headlamp! I needed to see what was going on!

Finally it dawned on me, “wrap it in an exception”. So now the code became:

try
{
$session = New-SFTPSession -ComputerName $sftpURL -Credential $Credential
}
catch
{
$exception = $_.Exception.Message
“Error in New-SFTPSession – $exception – ” | out-file ftp_log.txt -append
}

Now THIS got me somewhere. I was getting a timeout message!  Ok, that confused me. I mean I knew I had the right username and password, and it never timed out when I ran it. Why was it timing out when the service ran it?

So again, I did what I do in cases like this. Got up, went to the fridge, got some semi-dark chocolate chips and munched on them and ran through my head, “why was the secure FTP session timing out in one case, but not the other?”  At times like this I feel like Jack Ryan in Hunt for Red October, “How do you get a crew to want to get off a nuclear sub…”

Eureka!

DOH, it’s SECURE FTP. The first time it runs it needs to do a key exchange!  I need to accept the key!  At first I panicked though. How was I supposed to get the service to accept the key?  Fortunately it turned out to be trivial. There’s actually a parameter -Acceptkey. I added that… and everything ran perfectly.

Except the SSIS package

That’s a separate issue and again probably related to security and that’s my problem to debug today. But, I had made progress!

Summary

Now, quite honestly, the above is probably more detail than most care to read. But let me highlight a couple of things I think that are important when debugging or trouble shooting in general.

First, simplify the problem and eliminate the obvious. This is what I did with my first script. I wanted to make sure it wasn’t something simple and obvious. Once I knew I could write to a file using the service account that ruled out a whole line of questions. So I could move on to the next step.

Often I use an example from Sesame Street of “one of these things is not like another.”  In this case, I had to keep figuring out what was different between when I ran it and when the service account ran things. I knew up front that anything requiring keyboard input would be a problem. But, I thought I had that covered. Of course starting to compare and contrast the results of decrypting the cred.txt file showed that there was a problem there. And the issue with the initial acceptance of the SFTP key was another problem.

So, gather information and compare what works to what doesn’t work. Change one thing at a time until you narrow down the problem.

The other issue is being able to get accurate and useful information. Using debugging statements if often old school, and I know some developers look down on them, but often the quick and dirty works. So I had no problem using them. BUT, there are still cases where they won’t work. If for example a cmdlet hangs or doesn’t give output to standard output, they won’t work. Catching the exception definitely solved this.

The biggest problems I really ran into here is I’m still a beginner with PowerShell. I’m loving it, but I often still have to lookup things. But the basic troubleshooting skills are ones I’ve developed and honed over the years.

And quite seriously, sometimes don’t be afraid to walk away and do something else for a bit. It almost always brings a fresh perspective. I’ve been known to take a shower to think about a problem. Working from home that is easier for me than it might be for someone in an office. But even then, go take a walk. Or do something.  Approach the problem with a fresh mind. All too often we start down the wrong path and just keep blindly going without turning around and reevaluating our position.

When we teach crack and crevice rescue in cave rescue we tell our students, “If you’re not making progress after 15 minutes, let someone else try. The fresh approach will generally help.”

Looking back on this, the problems seem pretty obvious. But it took a bit of work to get there. And honestly, I love the process of troubleshooting and/or debugging. It’s like being a detective, compiling one clue after another until you get the full picture.

So now on to comment some of the code and then figure out why the SSIS package isn’t executing now (I have ideas, we’ll see where I get to!)

The Streisand Effect

I had originally planned on a slightly different topic for this week’s blog, but an email I received from my alma mater last night changed my mind.  First, a little background. I’m a 1990 graduate of RPI in Troy NY, a fact I’m quite proud of. Second, lately there has been a growing controversy over the shape and direction of the school administration, led by Dr. Shirley Jackson.  Let me say that I find Dr. Jackson’s credentials impressive and many of her initiatives have led RPI into the right direction for the 21st Century.

But (and you knew that was coming), all is not rosy in Troy (especially today as I write, it’s a dreary, cloudy day).

So let’s back up a bit though and discuss the Streisand Effect. Originally and mainly the effect refers to bringing unwanted attention to something by trying to suppress access to information in the first place. A similar reaction can be had by telling someone, “Don’t think about a pink elephant.”  Ok, how many of you were thinking of a pink elephant before I told you not to?  Now how many are thinking about one?  But I’m serious, please stop thinking about a pink elephant. There’s no such thing as a pink elephant. Ok, now I’m just being cruel about the whole pink elephant thing.  I’ll stop.

So, back to RPI. As I mentioned not everything is as pink and rosy as it might be.  Generally in cases like this, you have one of three choices. Hire a PR firm that advises you on a course of action, admit the issues and work to solve them, or to shut up and hope things blow over and people stop talking about it.

In this case the Alumni office at RPI decided to take the 4th option. They decided to send a letter to all alumni, including many who probably had no inkling of the ongoing controversies or if they did, didn’t care that it was going on.  The letter was written by an RPI professor in response to a set of well-written and researched articles and a website setup by a bunch of upset alumni. Like good RPI graduates, the alumni backed up their criticisms with research and data.  (For example the website notes how RPI’s credit rating has tanked over the years. An easily verifiable fact.)

The letter unfortunately did not address any of the data (except for one) and instead included highlights such as:

Could it be that the residual racism and sexism (no to mention heightism) that sits in the backs of the minds of the white male majority of our alumni makes it just a bit easier to see Dr Jackson as outside of her league, … out of her place?

Yes, somehow pointing out ongoing critical financial issues results in the Alumni office calling all alumni racists and sexists. Based on the reaction on several social media forums I’m on, after this letter, several alumni who were giving or thinking about giving have changed their minds.  I obviously know only a small subset of the thousands of alumni/ae who must have received this email.  But not a single one I know was convinced by this email to START donating to RPI. And at least one person who wasn’t aware of the majority of the issues said they were made aware as a result of this email and would stop donating.

So effectively, the RPI Alumni office has not only seriously insulted its donor base, it has brought attention to issues that many of the donor base apparently were not even aware of. Streisand Effect is now in full force!

I want to toss in one aside here to be clear: I am not ignorant of the fact, nor do I deny the fact that Dr. Jackson certainly has faced some pushback because of her identity. I’ve see comments about her skin color and gender and have pushed back against such comments. They’re not relevant to the issues at hand.  She has faced and overcome a great deal of discrimination and dislike simply because of who she is.  But, that does not make her or the Board of Trustees immune of criticism based on their actual actions and ones that are backed up by data. The drop in RPI’s credit rating is not due to who she is but rather the actions she and the Board of Trustees have made over the past 18 years.

In closing, as I step off my soapbox here; I realize this blog post is a bit off-topic from my usual fare, but it’s not really. It comes down to how we approach problems.  Trying to ignore them doesn’t necessarily make them go away, but shaming a wider audience doesn’t help either, it only brings more attention to the issue. If in 2003 Barbra Streisand had decided to simply drop the issue of the photographs of her home, the issue would have faded into the woodwork and most people wouldn’t have cared.

In 2018, if the RPI alumni office hadn’t blasted an insulting and condescending email, devoid of facts to its entire alumni base, fewer alumni would know about the issues. But I can guarantee now, many alumni that weren’t aware, or didn’t care, now do.

Think about this when trying to do damage control at your company.

Crane Operators

Talking online with friends the other day, someone brought up that crane operators in NYC can make $400-$500K a year. Yes, a year. I figured I’d confirm that before writing this post and it appears to be accurate.

At first glance one may think this is outrageous, or perhaps they chose the wrong field. I mean I enjoy being a DBA and a disaster geek, but I can’t say I’ve ever made $400K in one year!  And for what, I mean you lift things up and them down. Right?

Let me come back to that.

So, last night, I got paid quite a tidy bundle (but not nearly that much) for literally logging into a client computer, opening up VisualCron and clicking on a task and saying, “disable task”. On one hand, it seemed ridiculous;  not just because of what they were paying me, but because this process was the result of several meetings, more than one email and a review process.  All to say, “stop copying this file.”

But, this file was part of a key backup process for a core part of the client’s business. I had initially setup an entire process to ensure that a backup was being copied from an AIX server in one datacenter to a local NAS and then to the remote datacenter.  It is a bit more complex than it sounds.  But it worked. And the loss of a timely backup would impact their ability to recover by hours if not days. This could potentially cost them 100s of thousands of dollars if not into the millions.

So the meetings and phonecalls and emails weren’t just “which button should Greg click” but covered questions like, “do we have the backups we think we have?” “Are they getting to the right place(s)?” “Are they getting there in a timely fashion?”  And even, “when we uncheck this, we need to make sure the process for the day is complete and we don’t break it.”

So, me unchecking that button after hours, as much as it cost the company was really the end of a complex chain of events designed to make sure that they didn’t risk losing a LOT of money if things went wrong. Call it an insurance payment if you will.

Those crane operators in NYC? They’re not simply lifting up a beam here and there and randomly placing it someplace. They’re maneuvering complex systems in tight spaces with heavy loads where sudden gusts can set things swaying or spinning and a single mistake can do $1000s in damage or even kill people.

It’s not such much what they’re being paid to do, as much as how much they are being paid to avoid the cost of a mistake. I wasn’t paid just to unclick a button. I was paid (as were the others in the meetings) to make sure it was the right button and at the right time and that it wouldn’t cost even more.

Sometimes we’re not paid for what we do, as much as we’re paid for what we’re not doing.

 

This is secure, right?

WP_20180114_001So, over the weekend, while I was waiting for my wife’s hockey game to begin I was exploring the facility. I saw an upper level and I was curious if it was open. But alas no, it was locked. And I mean locked. Above is a photo of the chain and lock used to keep people out.

There’s a saying that locks only keep out honest people. That’s not entirely true, but there’s some truth to that. Security, especially in my field of IT and databases is extremely important. I am often working with data that contains private information about people. I have to take steps to keep it safe. So, as I mentioned in Too Secure, I don’t have a problem with security per se. There I talked about security that prevented me from doing my job.

Here we have the opposite. Making something that look secure, but really isn’t. I mean this has a heavy chain and a lock. I’m not sure who did this or why, but I’m sure they felt the lock was important. It’s not a cheap one either.

 

This is a case where probably a simple sign and string, “Balcony closed” would have sufficed.  The only purpose the lock really had was to make sure the chain wasn’t stolen. The purpose of the chain appeared to be to give something to attach the lock to.

Now, sure, someone could have removed a sign and string and said, “oh we never saw it” but again, what’s the ultimate risk if someone got upstairs? It was simply an observation balcony. They weren’t really securing much.

But I’m sure someone felt good about their security.

So, when you’re making something secure, stop and make sure you’re spending your time wisely. Sometimes not everything needs the most secure system available. Sometimes a “keep out sign” might be enough and easier.

Time, what is it?

Time is a funny thing. We all experience the passage of it, but, fundamentally it’s arbitrary.  In the SI system of units, the second is defined as “the duration of 9,192,631,770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom”,

Sure. That’s helpful. To someone. Not to me.

It’s now 2018 AD. Or is that 2018 CE? Or is it 4714, or 1438, or perhaps 5778 or perhaps it’s 2010. Or perhaps it’s year 1?

We can’t agree on what year it is. But we can certainly at least agree on some semblance of the date, correct? Not really. If I’m using Julian dates, my day is going to be 12 hours off of yours.

But, since I’m writing this in the Northeastern US, let’s agree we’ll work with the Common Era Calendar and that it’s January 9th, 2018.  That should work.  Everything should be simple right? For many of people, it probably is. For us programmers, it isn’t.

Let’s start with a simple sentence, “Run the job at midnight on Saturday.” Can we agree on when to run it? Most of us would probably run it just as Saturday is beginning. But some would argue that it’s just as valid run it at the end of Saturday.  I have in fact seen both arguments. This is where our common use of the language can fall apart. Fortunately though, we can remedy the issue. The military for example, for whom precision timing can be critical would say something like, “Run the job at 00:00 on Saturday.”  Now at least we’re in agreement.

But even then, we love to muck things up. Let me start with being a pedant for a second and point out that there is technically no such thing as 12 PM or 12 AM.  “What?”, you might say? “Of course there is!” Nope, sorry. Going back to the Latin PM and AM mean Post-Meridiem and Ante-Meridiem, i.e. after and before the middle of the day. So technically, what we call 12 Noon can’t be either (since it’s neither before nor after the time of the middle of the day, it IS the middle of the day). And equally, one can argue that 12 Midnight is neither.  And yet, you hear folks use the terms anyway.

Almost always, but not always though 12PM is associated with Noon and 12AM with Midnight. This follows because 12:00:01 IS after the middle of the day. So it makes sense to call the second before that also PM. Yet, that said, I have seen multiple times signs claiming that a place is open for dinner from say 7:00PM-12:00PM and or open “All Day from 7:00 AM-12:00PM” and they’re clearly open after lunch and even after dinner. (And by the way, does it strike you as a strange that we count 12AM, 1AM, 2AM etc to 11AM.. and start over at 12PM, 1PM… i.e. the 12 is considered part of the next set of hours even though we immediately move back to 1 after it? It’s one reason I really prefer a 24 hour clock.)

So I get pedantic about this when folks say 12PM or 12AM and ask them to clarify. This is especially important when it comes to scheduling tasks in say SQL Server.

BUT, we have yet another problem. I’m in the Eastern Time Zone of the US. You might be in the Pacific Time Zone of the US.  I want to hold a meeting at 3:00 PM.  Is that my time or your time?  Most modern scheduling software will correctly put this into my calendar as a 3:00 PM meeting and into yours as a 12 Noon meeting.

Yet that’s still not enough. I was recently reminded of this a few months ago as I started to schedule my PASS Summit into my calendar. I put the events in while I was in the Eastern Timezone, forgetting that when I got to Seattle, my calendar would “nicely” shift things for me 3 hours.  This meant rather than say thinking I wanted to attend the 9:00 AM Summit, I wanted to attend a 6:00 AM Summit.  Fortunately again, the software I discovered DOES let me specify what timezone the appointment actually is in when I set it up.

And one more aside. If we schedule a meeting at 3:00 PM (and we both agree on what that means) what happens if I say “Oh, I have a conflict, can we move that back an hour?” We we moving it backwards in time to 2:00 PM, or backwards in the sense of “further away” and mean 4:00 PM?

So, we can agree that hours are tough. But it can get worse.  Here’s a question for you. When was George Washington born? My father was always proud that he shared George Washington’s Birthday. But did he?  Was it February 22nd 1732? What if I told you he was born February 11th 1731? Both are technically correct (which is the best kind of correct.)

We all know about leap years. We have them because it doesn’t take exactly 365 days to orbit the sun. It takes closer to 365.25 days to orbit the sun. But even THAT isn’t really accurate enough. When Julius Caesar reformed the Roman Calendar, he added the concept of leap years. He know that without them, the calendar would slowly get out of synch with the seasons. Before you knew it, July would start becoming the middle of winter and who would want that! (Ok, technically it would take a few hundred years, but you get the point).

The problem was, scheduling Leap Years still wasn’t enough. By 1582, despite leap years, the calendar had “slipped” by 10 days.  So Pope Gregory instituted the current calendar most of the world uses which added the more complex rule of leap years every 4 years, unless the year is divisible by 100 which are not leap years, except in the cases of when it’s divisible by 400 when it is a leap year.

Now, Pope Gregory had a lot of influence over parts of Europe, so predominately Catholic countries adopted the changes almost immediately, in 1582.  But understandably, the more Protestant countries were a bit slower to adopt. The Great Britain and the Colonies didn’t adopt it until 1752, after George Washington’s birth. So, he was born on either day depending on which calendar you’re using. And by then things had slipped 11 days. (The change in years is a related, but different issue that had to do with what was considered the first day of the year. It wasn’t always January 1st you know.)

Now, when Y2K rolled around, I have to admit, I started to wonder, and still do, what would have happened if we had had computers in 1899. Would they have gotten the lack of a leap year correct? My guess is, “probably not”. And in fact, ironically enough, I recall at least one software package (I can’t recall the name) that is well prepared for 2100 and 2200 and 2300, but was NOT prepared for the year 2000 and in fact skipped the leap day that year. (Oh, by the way to really blow your mind, depending on the calendaring system, the leap day is actually February 24th. Or perhaps a more accurate way of saying it is that the leap day is inserted after the 53rd day of the year.)

In conclusion let’s just say time been on my mind a lot lately, partly because I’ve orbited the Sun just over 50 times now and because it’s a new Year. And as a developer, at times handling dates can be far tougher than it looks at first pass, you have to deal with midnight boundaries, timezones, leap years (or the lack of them) and even wholesale shifts in calendars (and this is ignoring completely different calendars such as the numerous lunar based ones that may or may not have entire leap months!) Oh and completely left out any mention of leap seconds! Yes, you can have December 31st 23:59:60 as a valid time.

But when you think about it, other than the fact that time keeps slippin’ (slippin’, slippin’) into the future, all measurement is really arbitrary.  We pick a 0 point and go with it. We just have to agree at some point (in time, yes, the irony is not lost on me) what that 0 point is and how we’ll measure from there, and we’re all good.

Or are we? I’ll leave you with this one final thought. Far into the future, possibly trillions of years, when the Universe has expanded so much that the distance between particles is far enough that none can interact, essentially nothing will be changing. Can you even have time if you have no clock against which to measure it? Will time eventually run out?

I could go on and on about time, but, I seem to have run out of it.