This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 temporal types with sub-second resolution
Title
Task ID173
Queue
Version
Status
Priority
Copies toMonty

Created bySergei09 Jan 2011Done
Supervisor   
Lead Architect    
Architecture Review 10 Jan 2011
Implementor 01 Mar 2011
Code Review  
QA  
Documentation  
 High-Level 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 - 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.
 Task Dependencies
Others waiting for Task 173Task 173 is waiting forGraph
 
 High-Level Specification
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)
 Low-Level Design
 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
This Task08080
Total08080
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Sergei - Wed, 01 Jun 2011, 15:46
    
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
 

(Sergei - Tue, 01 Mar 2011, 14:06
    
Code Review updated:  -> Monty
QA updated:  -> Pstoev

(Sergei - Tue, 01 Mar 2011, 13:39
    
QA updated: Hakan -> 

(Sergei - Tue, 01 Mar 2011, 13:39
    
Implementor signoff

(Sergei - Wed, 19 Jan 2011, 16:08
    
QA updated:  -> Hakan

(Sergei - Tue, 11 Jan 2011, 20:28
    
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)
 
 

(Sergei - Tue, 11 Jan 2011, 20:18
    
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.
 
 

(Sergei - Mon, 10 Jan 2011, 19:55
    
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
 

(Monty - Mon, 10 Jan 2011, 19:07
    
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'.
+
+
+
+
 

(Monty - Mon, 10 Jan 2011, 18:57
    
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.
+
 

(Monty - Mon, 10 Jan 2011, 18:54
    
Lead Architect updated:  -> Sergei
Architecture Review updated:  -> Monty
Architecture Review signoff

(Sergei - Sun, 09 Jan 2011, 20:08
    
High-Level Specification modified.
--- /tmp/wklog.173.old.28964	2011-01-09 20:08:54.000000000 +0000
+++ /tmp/wklog.173.new.28964	2011-01-09 20:08:54.000000000 +0000
@@ -1,2 +1,40 @@
+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 8+3=9 bytes.
+
+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.
+
+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.
 
 


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB