Comment On A Better Date Diff

Can I write code to count the months on my knuckles?

You can lead a horse to water, but you can't make it breakdance.

Well in a MVC architecture it is important to keep the abstractions cleanly separate.

CAPTCHA: consequat - This WTF is the consequat of adhering strictly to MVC design.

If I had a Golden Hammer
I'd Golden Hammer in the morning
I'd Golden Hammer in the evening
All over this land

Addendum (2010-03-17 14:35):
See also
http://sourcemaking.com/antipatterns/golden-hammer
and
http://en.wikipedia.org/wiki/If_I_Had_a_Hammer

Well duh, that's just time data. Where do you get data from? A database, stupid! Makes perfect sense...

dgvid:

If I had a Golden Hammer
I'd Golden Hammer in the morning
I'd Golden Hammer in the evening
All over this land

If I had a bell, I'd ring it in the morning
I'd ring it in the evening, all over this land.

And then I'd punch you in the face.

Myself:

dgvid:

If I had a Golden Hammer
I'd Golden Hammer in the morning
I'd Golden Hammer in the evening
All over this land

If I had a bell, I'd ring it in the morning
I'd ring it in the evening, all over this land.

And then I'd punch you in the face.

Seen on a bumper sticker: "If I had a hammer, there would be no folk singers."

CAPTCHA: suscipere: (latin) To suspect your father of patricide.

When all you have is a crimper, everything's a UTP cable

Aw isn't that cute. They let the DBA write some code.

"But an even rarer breed are those that, like Paul’s predecessor, who to use built-in code"

You might want to fix that.

I once saw code (in production) that used a stored procedure to calculate disk space on the server. I have no idea who wrote it, or why they did it that way, but it caused issues all the time.

Mark:

You can lead a horse to water, but you can't make it breakdance.

I think you mean "You can lead a horse to water, OWWW MY FREAKIN HEAD!!!!!"

frits:

Aw isn't that cute. They let the DBA write some code.

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

Well, on embedded systems without a file system...

Is this the date diff code that caused this post to be way late?

This is a great idea! You should wrap it in a web service so it can be called from other applications that need it.

Ha!. Not complicated enough. If our "lead" developers had their way, you'd have to call a remote service to make the database call.

Ironically the code takes DateTime arguments so it's hard to imagine that the programmer wasn't aware of a DateDiff function.

But what if the datediff in the database for whatever reason produces the wrong results from time to time?

"We want to be absolutely sure your numbers match the reports that the database generates". Well, there's one way to solve THAT problem... (especially after a long an tiring "how can you be sure" discussion with senior management)

Although, IF I were to come up with a solution like that I'd comment the hell out of it to make sure everyone would understand *why* this roundtripping was done.

Bart:

Ironically the code takes DateTime arguments so it's hard to imagine that the programmer wasn't aware of a DateDiff function.

But what if the datediff in the database for whatever reason produces the wrong results from time to time?

"We want to be absolutely sure your numbers match the reports that the database generates". Well, there's one way to solve THAT problem... (especially after a long an tiring "how can you be sure" discussion with senior management)

Although, IF I were to come up with a solution like that I'd comment the hell out of it to make sure everyone would understand *why* this roundtripping was done.

Everyone knows that certain version of SQL server calculate the datediff differently then certain version of your favorite framework. So if you want to make sure your dates match the database, you get it from the database.

What's the problem here?

SCSimmons:

frits:

Aw isn't that cute. They let the DBA write some code.

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

That hardly makes sense - if a primary key is functioning, why would you need to modify it, as opposed to adding a new index (clustered or not)? It sounds like the requestor is confused or asking for a quick way to wreck the database.

Bart:

Although, IF I were to come up with a solution like that I'd comment the hell out of it to make sure everyone would understand *why* this roundtripping was done.

And then comment it out so that while it's there in case management greps for it, it's not actually used. Translations between various date systems are just as much a solved problem as dates themselves.

BF:

Everyone knows that certain version of SQL server calculate the datediff differently then certain version of your favorite framework. So if you want to make sure your dates match the database, you get it from the database.

What's the problem here?

It doesn't have enough XML.

Bart:

"We want to be absolutely sure your numbers match the reports that the database generates". Well, there's one way to solve THAT problem... (especially after a long an tiring "how can you be sure" discussion with senior management)

This is probably closer to the truth.

Some PHB once saw a pre-alpha version that the dates were wrong in, then a couple months later heard about some difference between database time and framework time and decided that it was the same issue. So he demanded that all time be taken from the database so that the integrity of the reports could be maintained.

SCSimmons:

frits:

Aw isn't that cute. They let the DBA write some code.

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

And we're still waiting so quit browsing the web and give us our new column!!!

foxyshadis:

SCSimmons:

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

That hardly makes sense - if a primary key is functioning, why would you need to modify it, as opposed to adding a new index (clustered or not)? It sounds like the requestor is confused or asking for a quick way to wreck the database.

Theoretically I would agree with you, but I've had to do this before for an insurance company database, when after testing with sample data, the deployment to production revealed that social security numbers were not unique.

foxyshadis:

SCSimmons:

frits:

Aw isn't that cute. They let the DBA write some code.

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

That hardly makes sense - if a primary key is functioning, why would you need to modify it, as opposed to adding a new index (clustered or not)? It sounds like the requestor is confused or asking for a quick way to wreck the database.

Who said the PK was functioning properly? It's possible (probable) that this is to support some application change they want in production. Of course, the real question is who the hell made a PKEY with real data in it?

That's nothing. The code where I work queries the database once a second, to find out what time it is.

I kid you not.

Peter.

Peter Spierenburg:

That's nothing. The code where I work queries the database once a second, to find out what time it is.

I kid you not.

Peter.

How does it determine if it is time to query again?

usitas:

Peter Spierenburg:

That's nothing. The code where I work queries the database once a second, to find out what time it is.

I kid you not.

Peter.

How does it determine if it is time to query again?

Obviously it queries the database to find out what time it is and then uses the above procedure to determine if one second has gone by.

jasmine2501:

Theoretically I would agree with you, but I've had to do this before for an insurance company database, when after testing with sample data, the deployment to production revealed that social security numbers were not unique.

This is the funniest/scariest thing I've heard all day.

usitas:

Peter Spierenburg:

That's nothing. The code where I work queries the database once a second, to find out what time it is.

I kid you not.

Peter.

How does it determine if it is time to query again?

by querying http://time.com/atomic_clock.asmx

evilspoons:

jasmine2501:

Theoretically I would agree with you, but I've had to do this before for an insurance company database, when after testing with sample data, the deployment to production revealed that social security numbers were not unique.

This is the funniest/scariest thing I've heard all day.

They never were - SSN/DOB is supposed to be unique, but people swap digits (no check digit) and use each others' numbers enough that relying on SSN as being unique is just a bad idea. Also, your PK would then be PII, which is just bad in itself.

Clearly, the next iteration (after having read "Refactoring", of course...) would look like this:

public static int DateDiff(DateTime date1, DateTime date2, TimeDifference td)

{

  string sql = string.Empty;

  string oper = string.Empty;



  switch (td)

  {

    case TimeDifference.Hours:

      oper = "HOUR";

      break;

    case TimeDifference.Minutes:

      oper = "MINUTE";

      break;

    case TimeDifference.Seconds:

      sql = "SECOND";

      break;

    default:

      return 0;

    }



      sql = string.Format("SELECT DATEDIFF({0},'{1}','{2}')", oper, date1.ToString(), date2.ToString());



  return Convert.ToInt32(DataLayer.DataManager.executeScalar(

    sql, CommandType.Text, new NameValueCollection()));

}

BF:

Bart:

Ironically the code takes DateTime arguments so it's hard to imagine that the programmer wasn't aware of a DateDiff function.

But what if the datediff in the database for whatever reason produces the wrong results from time to time?

"We want to be absolutely sure your numbers match the reports that the database generates". Well, there's one way to solve THAT problem... (especially after a long an tiring "how can you be sure" discussion with senior management)

Although, IF I were to come up with a solution like that I'd comment the hell out of it to make sure everyone would understand *why* this roundtripping was done.

Everyone knows that certain version of SQL server calculate the datediff differently then certain version of your favorite framework. So if you want to make sure your dates match the database, you get it from the database.

What's the problem here?

The database isn't providing the date data, just doing the math. With this logic, why not have the dbase do all your math functions? I'm sure its floating point results differ from C# also. Better write some sql every time you want x / y.

Franz Kafka:

foxyshadis:

...

That hardly makes sense - if a primary key is functioning, why would you need to modify it, as opposed to adding a new index (clustered or not)? It sounds like the requestor is confused or asking for a quick way to wreck the database.

Who said the PK was functioning properly? It's possible (probable) that this is to support some application change they want in production. Of course, the real question is who the hell made a PKEY with real data in it?

If you must know, there is a long-raging debate among DBAs about whether or not separate PKs are a good idea or recipes for disaster. Web frameworks are in love with them, but they are late to the party.

Alex:

An easy enough concept in C# that’s implemented with a trip to the database.

On reading this, my first thought was he'd actually built a huge database containing all possible time differences.

Franz Kafka:

evilspoons:

jasmine2501:

Theoretically I would agree with you, but I've had to do this before for an insurance company database, when after testing with sample data, the deployment to production revealed that social security numbers were not unique.

This is the funniest/scariest thing I've heard all day.

They never were - SSN/DOB is supposed to be unique, but people swap digits (no check digit) and use each others' numbers enough that relying on SSN as being unique is just a bad idea. Also, your PK would then be PII, which is just bad in itself.

The relationship between SSNs and people is *supposed* to be 1:1 although in practice (do to change someone's number for various reasons) it ends up being many:1

The one thing it is not is 1:many

Simply because I use (illegally I might add) someone else's SSN doesn't mean it still isn't an identifier unique to them.

usitas:

Peter Spierenburg:

That's nothing. The code where I work queries the database once a second, to find out what time it is.

I kid you not.

Peter.

How does it determine if it is time to query again?

Obviously he uses this in a while loop:

void Pause(int seconds)

{

	//We could use seconds * 500, but show 1000/2 for clarity

	int halfPausetime = (seconds * 1000)/2;

        

	DateTime date1 = DateTime.Now;

        //Sleep for half the time or we miss our window

	Thread.Sleep(halfPausetime);

	DateTime date2 = DateTime.Now;





	sql = string.Format("SELECT DATEDIFF(SECOND,'{0}','{1}')", date1.ToString(), 

		date2.ToString());

        int secondSleep = Convert.ToInt32(DataLayer.DataManager.executeScalar(

		sql, CommandType.Text, new NameValueCollection()));

        Thread.Sleep((secondSleep * 1000) /2);



}

If this were Oracle 11g, this would be screaming fast with function result caching /s

You think this is funny HUH? I actually cleaned up such a code. Just imagine the rest of horrors.

jasmine2501:

foxyshadis:

SCSimmons:

Yah, you can just kiss my grits.

OK, I'm just cranky at the moment, because of the application developers who are asking me to add a column to the primary key of a 1M row table. In production. They don't seem to like my idea of doing some kind of testing first. I know, those darn curmudgeon DBAs!

That hardly makes sense - if a primary key is functioning, why would you need to modify it, as opposed to adding a new index (clustered or not)? It sounds like the requestor is confused or asking for a quick way to wreck the database.

Theoretically I would agree with you, but I've had to do this before for an insurance company database, when after testing with sample data, the deployment to production revealed that social security numbers were not unique.

Yay Fraud!

CAPTCHA: sagaciter - wisely, accurately, sagaciously.

I have to make a confession. I called into the DB for date related computations myself. Abeit in a more complex scenario to ensure consistency between what the user sees in the frontend and what the database is eventually going to do. Reimplementing the exact algorithm that the DB uses didn't seem it worth.

How about a Date to spend some Time with the Irish Girl.

A compound PK?
Developers are asking for changes to a primary key because... I mean are they trying to resolve some performance issue or something?

Am I missing something here, or are this poor man's devs engaging in serious WTFery? Were the devs thinking "index" and it somehow came out "primary key"?

I sense a whole host of WTFs in play.

The US Census Bureau beats this WTF: They make every citizen calculate their own DateDiff by making them enter their age as well as their birth date. No DateDiff needed by the Census Bureau.

"Number of days between two timestamps" is a very poorly-defined problem. That's the reason there are so many implementations.
Off the top of my head, all situations which I've run into:
- How many 24-hour periods?
- How many different dates are involved?
- How many "full days", taking daylight savings boundaries into account?
- How many midnights?
- How many noons?
- How many 12:01 AMs?
- How many different dates which pass through a particular time?
- All of those, plus or minus one
- All of those, plus or minus one, conditional on what hour the endpoints are at

Often, the real, correct, best thing to do, is to say "use the date function I know the other side is using, rather than implementing yet another one myself". That's what's usually happening when you see someone call another sysyem (like a database) to do the calculation for them.

This is clearly the result of an experienced coder who has had significant experience with Daylight Saving Time. When deploying .NET code to a variety of locations, Date math is notoriously prone to error. Finding the number of minutes between 3/7/2010 12:00 AM and 3/7/2010 6:00 PM returns different results depending on local system defaults and operating system configuration. This clearly cannot be allowed! We must get all those time calculations from the database so they are standardized properly!

Besides the obvious WTF of implementing this at all, there's a much worse sub-WTF logic error. This returns 0 when it is passed a value for TimeDifference it doesn't have a case for. 0 is a valid response to expect, which means there should not be code set up to interpret that as an error.

If TimeDifference is ever modified to contain more members and this function is not updated, it will fail silently until the unexpected behavior is noticed and tracked down.

Kinda funny, I HAD to to get the current time from the SQL server using a stored procedure vs. using C#'s DateTime. It's a synchronization application and the web server's clock may differ from the client AND the web server (where the c# code executes). The SQL server's clock is used since it's the one that's used for datetime stamping the records.

Datediff on the other hand...

The real WTF is the Census Bureau. All but one (arguably two) of the questions on that form are unconstitutional. Why do they need to know if I own my house, or my phone number, to make a head count for the House of Representatives? Why do they need to know my race-- have they not heard of the 16th amendment?

At least it's better than Clinton's ridiculous multi-page survey.