Clarion databases, so far I'm not a fan. Here is just one of the reasons.
Clarion Date
This is the easy one, the date is stored as the number of days since December 28, 1800. Where that date comes from, who knows, but that's the date. Add whatever number is in the database--as days--to that date and you'll get the date you're looking for (pun intended because we all know if you think this is cool you don't have a date).
It should also be noted
one of the things I read while looking into Clarion dates said even though the date is December 28, 1800 you still can't use numbers lower than 4. Which puts you at January 1, 1801... and is a date that makes only
slightly more since except now you have to worry about subtracting. For the purposes of programming or spreadsheets, I found it easier just to pretend this problem doesn't exist because all the dates I am working with are in the 20th and 21st century.
Clarion Time
Here is the can of worms, and the reason I'm writing this down. I couldn't find squat on the Internet about how Clarion stores time. I'm sure it's out there, I'm just saying the search results leave a lot to be desired. Here is what I would have wanted after mashing "How the heck does Clarion database store time?" into Google.
- It's in centiseconds.
- It starts at Midnight
- It rounds down (that part might just be my program)
Centiseconds, you know, 1/100th of a regular second. That's a perfectly sensible measurement of time no one really uses. Why even spell check wants to change it to
secondments. Doesn't really matter, as long as we know what it is we can worth some mathgic on it and make it make since.
Clarion's Time Field / 100 = Seconds from Midnight
Clarion's Time Field / 100 / 60 = Minutes from Midnight
Clarion's Time Field / 100 / 60 / 60 = Hours from Midnight
These work, but they give you a decimal number. For example if I have a time field with "2861193" and I convert it to hours I get 7.94775833 (and that's 7.94775833 in the morning). From that it's just normal convert decimal hours into useful time (drop the 7 and multiply by 60), which in this case is 7:56.87.
We use a proprietary ERP with a Clarion database at work, which is why all this is happening in the first place. When I take my result, rounded to the nearest minute it does not match. My result from the above example rounding to 7:57 and the program showing 7:56. Results that should round down work as expected, so I think it's one of two things. Either the start time is not exactly at midnight, or the program just rounds everything down. I assumed the later and will wait and see if people who use my program see a difference between the proprietary one. I figure if I am wrong, the problem will be very minor, and for the most part it's just not worth the time to troubleshoot any farther than I have--I checked like 8 of them! That's pretty good right?
Most importantly, I wanted to share this info with other who have found this frustrating. Probably not many, but you never know.
Conclusion
I made a
calculator to figure this out for my project. I've also made the
source available.
The Calculators accept user input, but the code was also used as a function for my project. In the future I may make a post about my solution to connecting to a Clarion database. It's a pain, it's not elegant, but it works. It was also one of those things I had a hard time finding help with.
