"Haisam K. Ido" <[EMAIL PROTECTED]> wrote on 07/06/2005 02:35:54 PM:
> I want to store a date of this format
>
> yyyymmddhhmmss.ssssss
>
> Should I use DOUBLE as the data type? Yes I do need all the .sss's. Or
> should I use DATETIME and then have another column to store the fraction
> of seconds?
>
>
>
>
For me, how this data would be stored would depend on what you will need
it for later. My first idea is to convert the yyyymmddhhnnss portion to an
integer using UNIX_TIMESTAMP() and add that integer value to your .ssssss
information to create and store the value as a double. That way you can do
direct subtraction to get the difference between any two times in seconds
or fractions of a second (as everything will be in the same unit).
If you leave it as a packed number, you will get some very odd results if
you try to do direct math on the value. Look at the mathematical
representation of the difference between two time values just one second
apart:
2005-07-06 12:01:59 packs as 20050706120159
2005-07-06 12:02:00 packs as 20050706120200
If we subtract those two values we get the value 41 not 1 as we might
want. However, if we first convert them through UNIX_TIMESTAMP(),
everything works as expected:
UNIX_TIMESTAMP('2005-07-06 12:01:59') = 1120665719
UNIX_TIMESTAMP('2005-07-06 12:02:00') = 1120665720
The difference between those two values is only 1 (second). IMHO, makes
this a much more convenient way to do "time math". Will this work for what
you need?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
