Time estimation for the work needed.

Server side

Adding second precision 6..9 to the server side will include:

1. Low level storage format – 3 days
2. get_date() changes (either extending the server-side MYSQL_TIME or switching to the recently added new data type API, e.g. val_native()) – 5 days
3. Comparison operators: packed longlong representation won't be enough to compare DATETIME(n>6), so all routines doing DATETIME/TIME comparison must be either implemented specifically for FSP=7..9, or reimplemented for all FSP=0..9. – 5 days
4. Binding new data types to MYSQL_TIME in embedded server. Currently memcpy() is used. Something else will be needed. – 3 days
5. Replication from old to new data types (and other way around) – 5 days
6. Low level parsing and data type conversion functions – 3 days
   • str_to_xxx()
   • number_to_xxx()
7. SQL functions (5 days). At least these functions will be affected:
   • Hybrid functions: CASE and CASE abbreviations (such as COALESCE, IF), LEAST, GREATEST
   • UNIX_TIMESTAMP()
   • TIME_TO_SEC()
   • CURRENT_TIME
   • CURRENT_TIMESTAMP
   • CURTIME()
   • NOW()
   • UTC_TIME()
   • SEC_TO_TIME()
   • CAST(expr AS TIME)
   • CAST(expr AS DATETIME)
   • TIMEDIFF()
   • MAKETIME()
   • SYSDATE()
   • FROM_UNIXTIME()
   • CONVERT_TZ()
   • TIMESTAMP()
   • EXTRACT()
   • ADDTIME()
   • SUBTIME()
   • DATE_ADD()
   • DATE_SUB()
   • STR_TO_DATE()
8. Low level class affected – 3 days
   • Type_handler_(time|datetime|timestamp)
   • Field_(timef|datetimef|timestampf)
   • Item_typecast_(time|datetime)
   • Item_cache_(time|datetime|timestamp)
   • Item_(time|datetime)_literal
   • in_(time|datetime|timestamp)
   • cmp_item_(time|datetime|timestamp)
9. Adjust existing tests (especially related to rounding vs truncations) – 3 days
10. Adjust system versioning code to use TIMESTAMP(9) rather than TIMESTAMP(6), e.g. tests for MAX_DATETIME_FULL_WIDTH – 1 day
11. Fix the server to allow opening both old tables with versioned TIMESTAMP(6) fields and new tables with versioned TIMESTAMP(9). Another option is to disallow opening old tables with versioned TIMESTAMP(6). – 1 day
12. Force automatic conversion of versioned fields from TIMESTAMP(6) to TIMESTAMP(9) on ALTER – 1 day
13. Possibly fix mysql_upgrade to extend versioned TIMESTAMP(6) to TIMESTAMP(9) – 2 days

Client-server protocol and Connector-C: – 10 days

1. Client-server protocol will need changes, to transfer nanoseconds.
2. The structure of the client-side MYSQL_TIME (that is used in the client C API) will need changes.
   Alternatively, a new structure (say MYSQL_TIME9) will be needed.
   A new structure is preferable, because we'll need a new structure soon anyway, for TIMESTAMP WITH TIME ZONE.
3. Changes in the bind/fetch code will be needed to address changes in #2

Note, similar changes will be needed in all connectors (ODBC, Java, PHP, Perl, etc)

Open questions

There is a also a problem that some operating systems do not provide clocks with nanosecond precision, so things like CURRENT_TIMESTAMP(9) won't be available on these operating systems. We'll perhaps have to zero-pad nanoseconds on these platforms.

Summary

Collecting all together: 50 man-days (~10 man-weeks)