Date handling, databases, and ramrods

Date handling, databases, and ramrods

Readers who are familiar with my older posts (whether directly, or through the book) might remember my "three tech types" idea. There's the "replayer", who uses other people's playbooks, the "investigator", who creates those playbooks, and the "ramrod" who acts like an investigator when they really should just stick to the playbooks. In particular, the "ramrod" tends to come up with solutions which make no sense and create bigger messes for other techs down the road.

Here's another ramrod story from the world of databases.

For some reason, this guy who was notorious for installing horrible things on customer boxes got the idea that he would start running reports against the company's big database. I forget exactly what got him going on this, but since I had experience with that by virtue of creating my many analysis tools, he came to me with his questions.

He also came to me with news, like when he was proud of having done something. Some of these accomplishments were dubious at best. One day, a chat window from this guy popped up in my face.

X: I just added "day of week" columns to the query for the (table) that match the created_date and closed_date

I responded, saying that I didn't understand, since Postgres will extract that for you. Still, he continued.

X: 2002-05-23 12:34:56 = "Thursday"

Groan, so he was using literal strings? He kept going.

X: In separate columns... didn't know pgsql had a function for that, I'm more an Oracle / MSSQL / MySQL type

He supposedly knows three different flavors of SQL implementations and he didn't go looking for a function? That's, what, 30 seconds with a search engine?

Now I was curious, so I prodded.

R: extract (dow from time) as dow

R: i'm doing something like that

R: did you actually write a doomsday function?

By "doomsday function", I meant something which implemented the Doomsday rule for mapping dates onto days of the week. I've written code like this before out of desperation, and know that any time I even think about doing it now, something is VERY WRONG.

That's why I was asking if he actually came up with his own way to map "2013-03-20" onto "Wednesday". Remember now, this guy is from the "ramrod" category, and so you know any solution is going to be completely crazy.

X: I used a kludge - awk the yyyy-mm-dd to date and have date convert it, seemed quicker than writing it myself

X: external system call from perl

I didn't ask for details, but I think we can probably infer what's happening from this. First, he has data coming back from Postgres. It's a timestamp, since the people who set up the schema for that particular database knew how to use the types properly. That means you get back something like this:

postgres=# select now()::timestamp;
            now             
----------------------------
 2013-03-20 14:55:33.484445
(1 row)

So then, he takes that string and washes it through awk, presumably to strip it down to just "2013-03-20", or maybe to split it up and move it around, making something like the US-style "03/20/2013", or who knows what.

Did you catch what he said earlier about an "external system call from perl"? This guy was already in a language which was built around the notion of crunching text and sliding characters around to suit you, and yet he shells out to awk to do his text mangling?

See, I told you he was nuts.

Then, after he has the YYYY-MM-DD, it's another external call to 'date' to mangle it into a weekday. I assume it was one of those things where you call date with the -d switch and pass it a string, like this:

$ date -d "2013-03-20"
Wed Mar 20 00:00:00 PDT 2013

Then, I imagine he took the abbreviated weekname and mapped it back to the full one, since notice what he said way back in his first example: "Thursday", not the "Thu" that date would give you.

Oh, and finally, he wrote all of this out as a comma-delimited file.

Did I mention that commas were common in our data? Lots of customers had names like "Foo Bar, Inc." or "Widgets, LLC" or whatever. Treating them as a separator was a recipe for extreme pain.

One thing which stands out at me, looking back at this scenario: at no point did this guy go "wow! that's so much better!" and rip out his awk + date crap for a simple "extract...". Oh no. He kept it in there.

postgres=# select extract (dow from now()::timestamp);
 date_part 
-----------
         3
(1 row)

See, it's easy!

If you're that attached to code just because you wrote it, even though it's completely pointless and wrong, you might just be a ramrod.