How computers handle Date/Time

I was developing a piece of software for a client in San Francisco. It was a simple piece that made data entry from paper forms easier. The output of the software had to be .csv file (comma delimited text files), so the data could be imported into another, larger database.

The software commanded, among other fields, a date field, and a time field. I obliged, since the required output for a different piece of software required it as well. On the first go around, the output for the ‘time’ field looked like this: If you entered 10:00 AM, it came out like: 1/1/1900 10:00:00 AM

He was very concerned about that, so even though I explained to him how databases handle time, and that the database that would be importing the data won’t care, and may need it in that format, I still changed it to be text that read: ‘10:00 AM’.

Computers handle dates and times as a numeric value of the time past since January 1st 1900 in days. Time is merely a fraction of a day. Were you to enter 9:00 AM into a ‘time’ field, the value stored in the database would be 0.375, or 3/8 of a day. If you view that number in ‘Short Time’ format, you’ll see 9:00 AM. If you view that number in ‘Date Time’ format, you’ll see 1/1/1900 9:00:00 AM. It’s important to know (and accept... so people have trouble with the accepting part) that no matter the format you see it, it’s still just 0.375. This goes with entering dates as well. A date field is also storing a time, whether you see it or not. Today’s date (4/8/2008) is stored in a database as 39546 (39,546 days since 1/1/1900). If the computer only stores an integer for the date, and there is no fraction of a day, then guess what time it is!!! That’s right!!! Midnight!!

So, after a successful delivery, my client once again noticed a ‘concern’. The date fields came over in the csv like this: If you entered 4/12/2008, it came out like: 04/12/2008 00:00

After explaining it several times to my client, he still wouldn’t accept it. So much so that he refused to test the import with the csv formatted the way it was. I changed both fields to be formatted text. Luckily, the software that was accepting the files had built in code to change any text back to its proper numeric value.

It's like believing the words of a letter change if you use a different font.

Update on Tuesday, April 8, 2008 by Micah Rousey

Can ya tell I've been working a lot?

Comments

[mab]

-sigh- ah the joys of knowing something that others can't live without, huh?