description: 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.

=>

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 (to TIME'13:50:17' or TIMESTAMP'2014-06-09 13:50:17' ).

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.