----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: temporal types with sub-second resolution CREATION DATE..: Sun, 09 Jan 2011, 20:08 SUPERVISOR.....: Sergei IMPLEMENTOR....: Sergei COPIES TO......: Monty CATEGORY.......: Server-Sprint TASK ID........: 173 (https://askmonty.org/worklog/?tid=173) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 80 WORKED HOURS...: 0 ESTIMATE.......: 80 (hours remain) ORIG. ESTIMATE.: 80 PROGRESS NOTES: DESCRIPTION: We will add a support for timestamps (and other temporal types) with a sub-second resolution. Syntax ^^^^^^ The syntax for declaring a column is the conventional TIMESTAMP(X) DATETIME(X) TIME(X) where X is the scale - that is, number of digits after the decimal dot. TIMESTAMP(0) is the same as the current TIMESTAMP. The existing syntax (without specifying the scale) will, of course, work as before. X=6 means microseconds, X=9 means nanoseconds, X=12 means picoseconds. The max supported value of X is [undecided], but will be no less than 6. Possibly, we will do 6 in this WL - because it keeps syntax and protocol changes to the minimum, microseconds are already supported there. If needed, we will increase the timestamp precision later - the implementation (see below) needs to keep it in mind. The time and datetime literals can be written with the fractional part, for example '2011-01-08 20:29:26.1234567890'. This is alrteady supported. Client-server protocol ^^^^^^^^^^^^^^^^^^^^^^ We will not change the binary client-server protocol. It only supports microseconds in the MYSQL_TIME::second_part - we will truncate longer timestamps to this precision. Show commands and I_S ^^^^^^^^^^^^^^^^^^^^^ SHOW COLUMNS and SHOW CREATE TABLE will show TIMESTAMP(X) as appropriate (similar to INT(N) and DECIMAL(M,N) types). I_S.COLUMNS will *not* show '(X)' in the DATA_TYPE column, but it *will* show it in the COLUMN_TYPE column. It will show X in the new DATETIME_PRECISION column. Functions ^^^^^^^^^ The following functions need to be fixed to support sub-seconds: CAST() - as DATETIME(X), etc CONVERT_TZ() SEC_TO_TIME() TIME_TO_SEC() MAKETIME() - perhaps? FROM_UNIXTIME() - probably not UNIX_TIMESTAMP() - probably not UTC_TIMESTAMP(), UTC_TIME(), NOW(), CURTIME() and synonyms - probably not, high-precision NOW() is a separate task P.S. See also http://forge.mysql.com/worklog/task.php?id=946 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)