Allow fractional seconds precision in TIME, TIMESTAMP and
DATETIME data types.

This feature is a requirement (not an option) in the SQL standard.
Oracle supports 9 post-decimal digits (nanoseconds).
SAP DB and DB2 and PostgreSQL support 6 post-decimal digits (microseconds).
SQL Server 2005 supports 3 post-decimal digits (milliseconds).
SQL Server 2008 DATETIME2 data type supports up to 7 post-decimal digits
(tenths of microseconds).

Our earlier agreement with another company required us to support at least 
microseconds "as in Oracle". That requirement is no longer in force.

-- Note added 2007-07-03, by Trudy Pelzer
PS is discussing this feature with <customer>. They require
9 fractional-seconds post-decimal digits (nanoseconds), so that
must be the minimum implemented.
-- note ends

Data type description    

---------------------    



TIME [(fsp)]                     /* e.g. TIME(1) */

TIMESTAMP [(fsp)]                /* e.g. TIMESTAMP(1) */

DATETIME [(fsp)]                 /* e.g. DATETIME(1) */



Throughout this document, 'fsp' means fractional seconds precision,

where fsp is an integer between 0 and 12.



The default value for fsp is 0, that is, "TIMESTAMP(0)" is the same

as "TIMESTAMP".



If the user specifies an out-of-range value for fsp,

then that's a syntax error: 1063 Incorrect column specifier.



Do not worry about TIMESTAMP [(n)] WITH TIME ZONE.    

 

Example:

CREATE FUNCTION ff (param1 TIME(1))        /* for a parameter */ 

RETURNS TIME(1)                            /* for a return */ 

BEGIN 

  DECLARE variable1 TIME(1);               /* for a variable */ 

  CREATE TABLE t (s1 TIME(1));             /* for a column */ 

  RETURN TIME '12:34:56.9';                /* for a literal */ 

END



Standard requirement

--------------------



The ANSI/ISO core (non-optional) requirements are:

* The TIMESTAMP data type must allow for fsp = 0 or fsp = 6, microseconds.

* The default fsp is 6.



The ANSI/ISO non-core (Feature F555 Enhanced seconds precision) requirements are:

* The fsp may be something other than (0 or 6).

* The TIME data type, not just the TIMESTAMP data type, may also have a fsp.



MySQL will support the core requirements and the F555 requirements,

except that: our default fsp will have to be 0, not 6.

Many applications would have to change if we said "henceforth TIMESTAMP

means TIMESTAMP(6)". And no, let us not make this a sql_mode=ansi option.



Maximum fsp = 12

----------------



Some people (e.g. the writer of BUG#27838 and the writer of a comment

on forge.mysql.com for this task) would be satisfied with maximum fsp = 3,

milliseconds.

So far there are no technical arguments for maximum fsp = 3.



Some other DBMSs have the standard default maximum fsp = 6, microseconds.

Since gettimeofday() delivers microseconds, most users would be satisfied.



One other DBMS has maximum fsp = 9, nanoseconds.

That's been a requirement in two customer requests.

The technical argument against anything with maximum fsp > 6 is:

current datetime arithmetic only supports maximum fsp = 6.

For example, "SELECT TIMEDIFF('2000:01:01 00:00:00',

                              '2000:01:01 00:00:00.000000001');"

causes warning 1292 "Truncated incorrect time value".



Performance Schema has a measurement in maximum = 12, pseudo-picoseconds.

This might matter for intervals.

The technical argument against maximum fsp = 12 is:

you can't store all possible values in an 'unsigned int' field.



Originally the requirement was maximum fsp = 9 for this worklog task,

but the current proposal is maximum fsp = 12. The arguments are:

* Changing for picoseconds is no more trouble than changing for

  nanoseconds/microseconds.

* It's theoretically possible that we'll be able to use picoseconds someday.

* If we do say today that MYSQL_TIME's second_part is nanoseconds/microseconds,

  we can't say tomorrow that it's picoseconds, that breaks too many

  applications.

Let's see if there are objections.



Perhaps the maximum fsp could be different for each data type,

but the current proposal is that it's the same for TIME, TIMESTAMP, and DATETIME.



Literals

--------



Allow TIME '12:34:56.123456789012'.



The MySQL Reference Manual mentions:

"A relaxed syntax is allowed: Any punctuation character may be used as the

delimiter between date parts or time parts."

That will no longer be possible, '.' must indicate that a fraction follows.

(The manual says elsewhere that MySQL "is also smart enough to allow

[a relaxed string format]". We may have to think about the wording.)

See also email thread "WL#946 and Changing time literal format" starting with

[ mysql intranet ]/secure/mailarchive/mail.php?folder=4&mail=26664



MySQL currently also allows YYYYMMDDHHMMSS or YYMMDDHHMMSS.

The proposal is to allow fractions, for example YYYYMMDDHHMMSS.nnnnnn.



MySQL also currently allows { ts timestamp } and { t time }.

The proposal is to allow fractions, for example

INSERT INTO t VALUES ({ ts '1998-05-02 01:23:56.123456789' } );

(The ODBC requirement is [.fff]. We want [.ffffffffffff].)



Treat like decimals    

-------------------    

    

If time_column is defined as TIME(6), then:

    

SET time_column = TIME '1.000001' will result in 1.000001. 

No truncation.    



SET time_column = TIME '1.0000005' will result in 1.1000001. 

Rounding.    

    

SET time_column = TIME '1.0000005' + 0.000001 will be exact. 

No float arithmetic.    

 

TIME '00:00:00.1' = TIME  '00:00:00.100' is TRUE.



Suppose timestamp_column is defined as TIMESTAMP(0).

Currently, if we say

SET timestamp_column = '2005-01-01 23:59:59.9';

The result is '2005-01-01 23:59:59.9', we truncate, we don't round.

This makes some sense, since changing the date part is drastic.

So there may be objections to rounding in future.





Functions and Operators    

-----------------------    

    

The feature will be tested with all functions and operators    

that accept or return temporal values.



Specifically:    

    

= > < >= <= + -.    

As in "WHERE time_col > '12:34:56.7890'"    

Sometimes MySQL converts constant timestamp strings to long

integers 'for faster comparison', but that will become hard.

    

ADDTIME().    

As in ADDTIME(time_col, '.05');    

Arithmetic with post-decimal digits already works for microseconds.    

    

CURTIME(), CURRENT_TIME(), CURRENT_TIME, UTC_TIMESTAMP(),    

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, NOW(),    

LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP().    

Don't do anything. Yes, we already allow CURTIME(6) but

the 6 is not a fsp, it's ignored. Yes, CURTIME()+0 results

in a double with precision = 6 but fsp = 0, always.

Yes, these functions should allow "[(fsp)]".

Perhaps someday, as a separate task, MySQL should use

"More precise functions for getting time" (see later section).

DEFAULT CURRENT_TIMESTAMP(5) should be legal in column definitions.



CAST()

Allow "CAST(expr AS { TIME | TIMESTAMP | DATETIME } (fsp))".

When casting to or from other data types that have digits    

after a decimal point (i.e. DECIMAL, NUMERIC, FLOAT, REAL,    

DOUBLE PRECISION), preserve post-decimal digits.    



DATE_FORMAT().    

This must return a correct post-decimal value, rounded appropriately,    

if the argument is '%f'. But we document that %f means 6 digits, not 12.    

People might expect that there should be a way to specify how many

post-decimal digits we want, but forget that.



EXTRACT(MICROSECOND ...).

Extracting post-decimal digits from string literals already works,

for microseconds.

    

GET_FORMAT().    

This must return correct post-decimal value, rounded    

appropriately, if the argument is '%f'.    

    

MAKETIME().    

This won't work with hour,minute,second,microsecond    

arguments. Forget it.

    

MICROSECOND().

This must work just like EXTRACT(MICROSECOND ...).    

There will be no PICOSECOND() or NANOSECOND() or MILLISECOND()

or CENTISECOND() functions.

    

SEC_TO_TIME(seconds).    

This will continue to accept seconds only, not    

seconds with fractional precision. Forget it.    

    

SUBTIME().    

Arithmetic with post-decimal digits already works for microseconds.    



TIME(expr).    

If the expr contains post-decimal digits, round or truncate.    

    

TIMEDIFF().    

Arithmetic with post-decimal digits already works for microseconds.



TIMESTAMP(expr).    

If the expr contains post-decimal digits, round or truncate.    

    

TIMESTAMPADD().    

Arithmetic with post-decimal digits already works for microseconds.    

    

TIMESTAMPDIFF().    

Arithmetic with post-decimal digits already works for microseconds.    

    

TIME_FORMAT().    

Accept '%f' specifier and handle it appropriately.    



TO_SECONDS).

This doesn't exist yet, WL#3352 mentions it. There will be no

corresponding TO_PICOSECONDS() function.



Connectors and API    

------------------

    

We will not ask the connector team whether    

they can handle fractional seconds precision for time/timestamp.    

If they can't, that will be reported as a connector bug.

For example, if somebody adds extra '0's for

java.sql.Timestamp when there already are extra '0's,

that's somebody else's problem.

For example, the MySQL documentation says

"To improve the integration between Microsoft Access

and MySQL through Connector/ODBC: ... You should have a

TIMESTAMP column in all tables that you want to be able

to update." and it's fine with us if that means TIMESTAMP(0).



For C API prepared statement data types, in MYSQL_TIME structure,    

we have "unsigned long second_part". We don't use it. We won't --    

using it is not part of this task. But the manual says it's

"The fractional part of the second in microseconds."

http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statement-datatypes.html

... and that will be wrong if MySQL actually allows picoseconds.



Metadata    

--------    

    

For SHOW COLUMNS / DESC / SHOW FIELDS, we    

could show "TIME(n)" in the Type field. But Peter thinks    

it's unnecessary. Forget it.    

    

For SHOW CREATE TABLE, it is necessary to show the TIME

DATETIME and TIMESTAMP  data types correctly, with "(fsp)" if fsp > 0.    

    

For INFORMATION_SCHEMA.COLUMNS, we need a new column,    

DATETIME_PRECISION, BIGINT, UNSIGNED. This column contains the

fsp for TIME/TIMESTAMP/DATETIME, and NULL for anything else.

In correct standard SQL this column would come right after NUMERIC_SCALE;

however, we'll have to put it at the end in order to avoid

a certain customer's wrath.



Implementation 

--------------    



TIMESTAMP(1) columns are distinguishable from TIMESTAMP(0) columns,

and could be stored differently. For example, we could store them as

DECIMAL(24,12) with number of picoseconds since 12:00:00 UTC 

on November 24 4714 BC (Gregorian). But then we'd need extra bits

to indicate whether the year part is '0000', the month part is '00',

or the day part is '00'. This should be considered now, since we

don't want to change the storage later. But we'll probably reject it.



More likely we'll use [VAR]CHAR strings most of the time.

The only requirement is that the strings can be longer.



Storage engines that doesn't use a [VAR]CHAR variant for datetimes

will have trouble with the new requirements. For example, MyISAM has

TIME as 3 bytes (days*24*3600+hours*3600+minutes*60+seconds) 

DATETIME 8 bytes (year*10000+month*100+day,hour*10000+minute*100+second) 

TIMESTAMP 4 bytes (seconds since 1970-01-01) 

We can't squeeze in fractional-seconds information in such small spaces,

so we need one or more new internal data types.



TIMESTAMP(1) will still have the same range constraint as TIMESTAMP(0),

for example dates in 1969 are illegal. There won't be good reasons for this

limitation if we store a different way, but people are used to the constraint.

And @@timestamp will continue to be the number of seconds since 1970-01-01

00:00:00.



Other products

--------------



DB2 TIMESTAMP fsp = 1/1000000 second.

ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_US/db2s1e90.pdf

Four bytes date, 3 bytes time, 3 bytes microseconds

This applies only for TIMESTAMP; DB2 doesn't have fsp for TIME.



Oracle 11g TIMESTAMP precision = up to 1/1000000000 second.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330

One byte century, one byte year, one byte month, one byte day,

four bytes fsp (ignoring offsets).



SQL Server 2005 DATETIME precision = 1/300 second.

http://msdn2.microsoft.com/en-us/library/ms187819.aspx

Two 4-byte fields, first is (signed) days since 1900-01-01, second is

1/300-second increments since midnight.



SQL Server 2008 DATETIME2 precision = 1/10000000 second.

http://msdn2.microsoft.com/en-us/library/bb677335(SQL.100).aspx

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266498,00.html



More precise functions for getting time

---------------------------------------



We will have new timers due to WL#2373.

A look at performance_schema.PERFORMANCE_TIMERS

will tell you what has acceptable frequency and

resolution and overhead. Where the nanosecond

timer is nonexistent or not acceptable, as in

Linux and Windows, we can simulate by using

both the microsecond and cycle counters and

saving counts in global variables. For example:

let x = time in microseconds * 1000.

let y = cycle counter.

if (x == last_microsecond_time)

  let x = x + (y - last_cycle_counter)

let last_microsecond_time = x

let last_cycle_counter = y

return last_microsecond_time

However, this is not necessary, as long as

the execution of any SQL statement takes

more than one microsecond.



There may be a desire that time will go up monotonically.

We're not guaranteeing that. But we could consider it as an option.

There was a conversation recently on NDB threads about clock_gettime

overhead when you try to ensure that, see

[mysql

intranet]/secure/mailarchive/mail.php?folder=113&mail=16739/mail.php?folder=113&mail=16739.

Outside a cluster environment,

we could have a thread that gets activated once a second and sets

a global variable x = time-of-day-to-nearest-second, and we'd

increment x every time we used it.



The old meaning of TIMESTAMP(n)

-------------------------------



We used to allow (but ignore) "TIMESTAMP(n)". Unfortunately,    

before version 4.1, the (n) was display width rather than fsp.

Therefore we will break some application which was written for

MySQL 4.0  with display width in mind.

TIMESTAMP(n) was deprecated in MySQL 5.1, disallowed in 6.0.

So the confusion with the old behaviour should be minimal.



References 

---------- 

 

WL#974  Issuing an error message if wrong 

        datetime / date /time constant is used 

WL#1872 Add support for dates from 2038 till 2116 

        as values of TIMESTAMP type 

BUG#8523 Microseconds precision is not retained by TIME,

         DATETIME, and TIMESTAMP fields

BUG#27838 DateTime and Timestamp do not work with Milliseconds (Duplicate)



Customer Reference

------------------



[ The customer name has been removed. See progress notes for 2009-08-06. ]

    

(Customer X) had requested this feature for Milestone 2.

Patrik Backman confirmed (email to pgulutzan dated 2005-03-08):   

"Yes, the fractional seconds will be needed for (customer X)."

Apparently (customer X) requirements no longer matter.

Current Tags

You must be logged in to tag this worklog