DATETIME does indeed store milliseconds. One of our applications uses it. The table that has the DATETIME column also has two computed columns for the date and time: DEXTRACT() and TEXTRACT().
(In 9.1) If the TIME format includes a decimal point (even "HH:MM:SS."), time or datetime differences are expressed in milliseconds, otherwise seconds. If the difference is in msec, then 24 days (=2,073,600,000 msec) exceeds the R:Base limit for integer values. So, for longer differences use a DOUBLE data type, which provides up to fifteen digits. 10^15 msec is more than 30,000 years. Similarly, if you are adding time to a TIME or DATETIME value, you will be adding sec or msec depending on the TIME format. Quick story illustrating why you should never use DATETIME for a unique identifier: We hired an experienced, reputable firm to create a web commerce app for us. They used a 12-digit order number. We asked them to reduce it to 9 digits so we could use (R:Base) integers in our application. At that time, I noticed that the order number was incrementing proportionally to the time, at a rate of 1 per 60 microseconds. I pointed out that decreasing the order # to 9 digits would increase the probability of a duplicate order # by a factor of 1,000. They said they'd get back to me. A few weeks later we got two orders with identical order numbers. To say the least, I was surprised that they had no primary key based on order #! Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, October 17, 2012 5:56 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: DateTime tutorial I would not depend on storage to the millisecond. I would recommend a DATE field and an autonumber. Select the range of dates and order by the autonumber. DATETIME is better when you really want to compare 2 date + time entries. Also, I may be wrong, but my tests in the past have indicated that DATETIME does not store miliseconds. Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of William Stacy Sent: Wednesday, October 17, 2012 4:13 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: DateTime tutorial OK then I figured autonumbers are here to stay, but for things that need to also be date/time stamped, is there any preference for the single DateTime data type vs. the separate Date and Time data types (I'm thinking in terms of storage overhead, query speeds, indexing, ordering, etc.)? On Wed, Oct 17, 2012 at 11:08 AM, William Stacy <[email protected]<mailto:[email protected]>> wrote: Are there any advantages to using one DateTime column over 2 separate columns of Date and Time? Also, I'm wondering if DateTime or Date+Time stamps could be used instead of autonumbering with it's rules etc.? It seems to me that it would be rare to have any duplications of DateTime or of Date+Time if time is stored to milliseconds, unless rows are being added programmatically as opposed to by key data entry. -- William Stacy, O.D. Please visit my website by clicking on : www.FolsomEye.com<http://www.FolsomEye.com> -- William Stacy, O.D. Please visit my website by clicking on : www.FolsomEye.com<http://www.FolsomEye.com>
