Add precision specification to TIME and TIMESTAMP in datatype and cast
----------------------------------------------------------------------
Key: CORE-4459
URL: http://tracker.firebirdsql.org/browse/CORE-4459
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Mark Rotteveel
Add support for specifying the precision of a TIME or TIMESTAMP in datatype
declarations (columns, domains, etc) and casts.
The definition would be (from SQL:2011, section 6.1):
<datetime type> ::=
DATE
| TIME [ <left paren> <time precision> <right paren> ]
| TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
Where the precision is 0-4. And the assumed default if not specified is 4 for
both.
Declaring TIME or TIMESTAMP will assume the full precision of 100 microseconds,
while TIME(0) or TIMESTAMP(0) is second precision, TIME(3) or TIMESTAMP(3)
millisecond precision and TIME(4) or TIMESTAMP(4) is the (current) maximum
precision of 100 microseconds equal to the unspecified.
Cast functionality should truncate the TIME or TIMESTAMP to the specified
precision, the behavior for a client application must be transparent for
compatibility reasons (eg a client setting a value of TIME'13:50:17.4971' to
TIME(0) or TIMESTAMP'2014-06-09 13:50:17.4971' to a TIMESTAMP(0) should be
allowed, and the value should be truncated.
SQL:2011 specifies the following syntax rule:
34) If <time precision> is not specified, then 0 (zero) is implicit. If
<timestamp precision> is not specified,
then 6 is implicit.
However given the current implementation of TIME (and the existing
implementation limit of precision 4), we should probably use 4 as the default
for both.
Rules for retrieval assignment (SQL:2011 Foundation, 9.1):
q) If the declared type DT of T is datetime, then:
i) If only one of DT and the declared type of V is datetime with time zone,
then V is effectively replaced by
CAST ( V AS DT )
ii) Case:
1) If V is a member of the declared type of T, then T is set to V.
2) If a member of the declared type of T can be obtained from V by rounding or
truncation, then T is set to that value. It is implementation-defined whether
the approximation is obtained by rounding or truncation.
3) Otherwise, an exception condition is raised: data exception — datetime field
overflow.
Rules for store assignment (SQL:2011 Foundation, 9.2):
xvi) If the declared type DT of T is datetime, then
1) If only one of DT and the declared type of V is datetime with time zone,
then V is effectively replaced by
CAST ( V AS DT )
2) Case:
A) If V is a member of the declared type of T, then T is set to V.
B) If a member of the declared type of T can be obtained from V by rounding or
truncation, then T is set to that value. It is implementation-defined whether
the approximation is obtained by rounding or truncation.
C) Otherwise, an exception condition is raised: data exception — datetime field
overflow.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
