maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              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)