This question is almost more of a logic puzzle, but here goes:
We have a database that stamps each new record with a unique number - today’s date and time represented as:
YMMDDHHmmSS
The number is easy to create and will always be unique since only one person has the ability to create a new record.
Here’s the question:
Can you represent the same “number” using fewer than the current 11 digits? Because some positions in this “number” will only be a zero or a one (like the first “month” or “hour” digit), it seems that you could make use of the wasted “2 through 9” entries that would never normally get set.
Anyone have a clever idea for reducing 11 digits down to 8? or 7? by “compressing” the date stamp into a shorter string of digits? We would like to keep it “numeric”, so converting to hex isn’t the answer we want (the A through F would cause the number to be saved as text).
Thanks in advance for any suggestions,
David
P.S. I know there are other methods for doing something like this (i.e. saving a value in a profile document and updating it to create a sequential number, or searching a view to find the last number used and adding one, etc), but I’m more interested in the answer to the “compressing date/time stamp” than finding an alternative method. This is almost more of a puzzle that I want the answer to than an actual application solution.
Subject: Reduce digits used in unique time stamp
Assuming that you are planning to use this as part of a unique key, it will have to be stored as a string of characters. I would strongly advise against trying to remove “unnecessary” zeroes out of the middle of the string. How would you recognize the difference between 1:11 and 11:01? They would both abbreviate to “…111…”
Subject: RE: Reduce digits used in unique time stamp
Doug,
I’m not suggesting removing zeros really (mostly because of the reason you offer). It has more to do with the fact that some place-holders will always only be either a zero or a one. In your example, the time 11:01 is valid, as is 01:01, but 31:01 will never occur (with the way I’m currently handing the hour). Why not make use of that “3” to represent something else?
But I think the best suggestion so far is to just reduce the whole thing down to seconds as Stan already suggested.
Subject: Reduce digits used in unique time stamp
The most compact unambiguous decimal value you can have with a one-second resolution is the number of seconds elapsed since a base time. That would give you a max displayed length of 9 digits for almost 30 years from the base time. It will be stored in 8 bytes (as a double).
Subject: Reduce digits used in unique time stamp
You could use the day number (3 digits) instead of YMMDD
Subject: RE: Reduce digits used in unique time stamp
Oops, I forgot the year! Oh well that’ll give you four instead of five digits