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 - support an optional
argument that specifies precision. e.g. NOW(6).

P.S. See also http://forge.mysql.com/worklog/task.php?id=946

Replication
^^^^^^^^^^^

We need to extend statement based replication to also have sub seconds.

parser: trivial, nothing to say here

frm: X naturally goes into the field_length slot in frm file,
  see e.g. open_binary_frm ("field_length= uint2korr(strpos+3)").
  The only detail to remember - force field_length=0 in old
  (4.0 and earlier) frms. Or ignore the problem as nonexistent
  (which it, most probably, is).

storage:
  from the engine point of view the type of column will be a fixed-width
  type BINARY(N), so the new data types will be automatically supported by
  all "normal" engines. For all types, it will be stored as

    <value w/o sub-seconds><sub-seconds part>

  where the first part is stored using the same format as before,
  and <sub-seconds part> is stored as a number, high-endian, number of
  bytes depending on X. For example
  
   TIMESTAMP(3) will be stored in 4+2=6 bytes.
   DATETIME(6) will use 7+3=9 bytes. (Note, not 8+3, as before one byte was
unused and we can get this back now)


Item:
  sometimes MySQL tries to pass around (and compare) the time as an integer
  internally. Item should be smart enough to disable this when sun-second
  part may be  present (This is needed as longlong is not big enough to hold
  a full date with sub seconds).

Field:
  probably we'll need new Field classes, Field_timestamp_w_subsec etc.
  Otherwise many methods will need to become conditional, such as

  enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
  enum Item_result cmp_type () const { return INT_RESULT; }
  bool can_be_compared_as_longlong() const { return TRUE; }
  etc.    
    
mysql_com:
  we will use the same MYSQL_TYPE_TIMESTAMP, etc constants as before.

log_event.cc: (replication log)
  The header size used for each statement is stored in the start event for
  each log. This allows us to easily extend the header and still keep the log
  format backward and forward compatible.
  In Format_description_log_event::write(IO_CACHE* file) we have to store the
  new log entry length in buff[ST_COMMON_HEADER_LEN_OFFSET]. The old length
  was LOG_EVENT_HEADER_LEN.
  In Log_event::write_header(IO_CACHE* file, ulong event_data_length) we have
  to add 3 bytes to store sub seconds.
  In Log_event::Log_event(const char* buf,
                     const Format_description_log_event* description_event)
  we have to read it into 'when + sub seconds'.

Notes:
* We need to make sure that partitioning works with new types.
* Try to still pass around timestamps as numbers whenever possible (e.g. up to
milliseconds)

Status updated.
--- /tmp/wklog.173.old.28648	2011-06-01 15:46:33.000000000 +0000
+++ /tmp/wklog.173.new.28648	2011-06-01 15:46:33.000000000 +0000
@@ -1,2 +1,2 @@
-Assigned
+In-Progress
 

Code Review updated:  -> Monty
QA updated:  -> Pstoev

QA updated: Hakan -> 

Implementor signoff

QA updated:  -> Hakan

High-Level Specification modified.
--- /tmp/wklog.173.old.7049	2011-01-11 20:28:27.000000000 +0000
+++ /tmp/wklog.173.new.7049	2011-01-11 20:28:27.000000000 +0000
@@ -53,6 +53,9 @@
                      const Format_description_log_event* description_event)
   we have to read it into 'when + sub seconds'.
 
-We need to make sure that partitioning works with new types.
+Notes:
+* We need to make sure that partitioning works with new types.
+* Try to still pass around timestamps as numbers whenever possible (e.g. up to
+milliseconds)
 
 

High-Level Specification modified.
--- /tmp/wklog.173.old.6633	2011-01-11 20:18:04.000000000 +0000
+++ /tmp/wklog.173.new.6633	2011-01-11 20:18:04.000000000 +0000
@@ -53,7 +53,6 @@
                      const Format_description_log_event* description_event)
   we have to read it into 'when + sub seconds'.
 
-
-
+We need to make sure that partitioning works with new types.
 
 

High Level Description modified.
--- /tmp/wklog.173.old.21438	2011-01-10 19:55:22.000000000 +0000
+++ /tmp/wklog.173.new.21438	2011-01-10 19:55:22.000000000 +0000
@@ -53,8 +53,8 @@
 MAKETIME() - perhaps?
 FROM_UNIXTIME() - probably not
 UNIX_TIMESTAMP() - probably not
-UTC_TIMESTAMP(), UTC_TIME(), NOW(), CURTIME() and synonyms - probably not.
-To get a high precision  NOW() on should use NOW(6) (microseconds = 6)
+UTC_TIMESTAMP(), UTC_TIME(), NOW(), CURTIME() and synonyms - support an optional
+argument that specifies precision. e.g. NOW(6).
 
 P.S. See also http://forge.mysql.com/worklog/task.php?id=946
 

High-Level Specification modified.
--- /tmp/wklog.173.old.17477	2011-01-10 19:07:34.000000000 +0000
+++ /tmp/wklog.173.new.17477	2011-01-10 19:07:34.000000000 +0000
@@ -18,12 +18,15 @@
   bytes depending on X. For example
   
    TIMESTAMP(3) will be stored in 4+2=6 bytes.
-   DATETIME(6) will use 8+3=9 bytes.
+   DATETIME(6) will use 7+3=9 bytes. (Note, not 8+3, as before one byte was
+unused and we can get this back now)
+
 
 Item:
   sometimes MySQL tries to pass around (and compare) the time as an integer
   internally. Item should be smart enough to disable this when sun-second
-  part may be  present.
+  part may be  present (This is needed as longlong is not big enough to hold
+  a full date with sub seconds).
 
 Field:
   probably we'll need new Field classes, Field_timestamp_w_subsec etc.
@@ -37,4 +40,20 @@
 mysql_com:
   we will use the same MYSQL_TYPE_TIMESTAMP, etc constants as before.
 
+log_event.cc: (replication log)
+  The header size used for each statement is stored in the start event for
+  each log. This allows us to easily extend the header and still keep the log
+  format backward and forward compatible.
+  In Format_description_log_event::write(IO_CACHE* file) we have to store the
+  new log entry length in buff[ST_COMMON_HEADER_LEN_OFFSET]. The old length
+  was LOG_EVENT_HEADER_LEN.
+  In Log_event::write_header(IO_CACHE* file, ulong event_data_length) we have
+  to add 3 bytes to store sub seconds.
+  In Log_event::Log_event(const char* buf,
+                     const Format_description_log_event* description_event)
+  we have to read it into 'when + sub seconds'.
+
+
+
+
 

High Level Description modified.
--- /tmp/wklog.173.old.16565	2011-01-10 18:57:08.000000000 +0000
+++ /tmp/wklog.173.new.16565	2011-01-10 18:57:08.000000000 +0000
@@ -53,9 +53,14 @@
 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
+UTC_TIMESTAMP(), UTC_TIME(), NOW(), CURTIME() and synonyms - probably not.
+To get a high precision  NOW() on should use NOW(6) (microseconds = 6)
 
 P.S. See also http://forge.mysql.com/worklog/task.php?id=946
 
+Replication
+^^^^^^^^^^^
+
+We need to extend statement based replication to also have sub seconds.
+