I'm working on a system that deals with data with second resolution. The data type we are using is not the MySQL DATETIME but the MySQL UNSIGNED INT and we save the datetime as UNIX_TIMESTAMP. By this way we managed to reduce by 4 bytes the storage needs of each record. We also reduce the index size as the datetime is part of our primary key.
Now there is the need to go even more precise and we need to go to millisecond resolution in the datetime. I have been checking the MySQL documentation and I couldn't find a predefined way to do it. One idea could be to have an additional field storing the milliseconds and have it also in the primary key. Before changing the design I would like to check with you. Maybe you have faced the same problem and you have found a better solution. Thank you in advance. Jose.
