Encoding the Date and Time as numbers

Recently I started working on a project that need the usual date and time information stored in a database. Now if you've used WebCatalog for any time, you know about the fun of doing searches on date fields and sorting date fields.

I remembered reading on the list (from Ken?) a technique for storing dates and times as numbers, making storing, searching and sorting very simple operations. I couldn't find the exact post, so I ended up reinventing the wheel, and thought I'd post the technique here. This technique allows you to store both the date and the time of an event in a single field, and recall either out again. This is a little longer to code when you want to display the date or time, but it makes the searches a lot more efficient.

This was written and tested on a Macintosh server, but should work on the other platforms as well...

Days since 0/0/00:
[math]{[date]}[/math]
this should be 6 digit number, like 731214.
If you are going to have to search by date, stop here.
It is also very easy to use math tags on this number if you need to calculate a date in the past or the future.
You can get the standard looking date back with this code:
[Format Days_To_Date %m/%d/%Y][calcTS][/format]

Fact: there are 86,400 seconds in a day. (can you account for all of yours?)
Seconds since 0/0/00 to last midnight:
[math]({[date]}*86400)[/math]
this should be a much bigger number, like 63168163200

Seconds Since Midnight:
[math]({[time]})[/math]
number should be between 0-86399

encode the date and time as the number of seconds since 1/1/000
[math]calcTS=({[date]}*86400)+({[time]})[/math]
Again, a rather intimidating number, should look like 63168239184

Now you can store calcTS in a database as a record create/modified time stamp.

What's that, you want to extract the date and time out of that number? Well, OK...
First we have to know the number of whole days:
[math]floor(calcTS/86400)[/math]

With that we can calculate the date, and format it anyway we want:
[Format Days_To_Date %m/%d/%Y][math]floor(calcTS/86400)[/math][/format]

how many seconds were left over?
[math]calcTS-(floor(calcTS/86400)*86400)[/math]

Not very readable, let's format the time:
[Format Seconds_To_Time %I:%M:%S %p][math]calcTS-(floor(calcTS/86400)*86400)[/math][/format]

This code makes it very easy to quickly sort large lists by date, or date&time fields. (Otherwise WebCatalog has to do this same operation everytime you execute a search with a fieldtype=date to each record anyway, which creates huge operational overhead.)

For those of you too lazy to type these examples in (!), I've made a simple page to download. (Mac .sit file)