gbase8s之current和sysdate的区别

在不开启USEOSTIME=1的时候，秒之后的精度全部显示0000

开启之后

以下是current和sysdate相关的时间默认值的设置

CREATE TABLE new_acct (

col1 INT,

col2 DATETIME YEAR TO DAY DEFAULT CURRENT YEAR TO DAY

);

CREATE TABLE tab1 (

id SERIAL,

value CHAR(20),

time1 DATETIME YEAR TO FRACTION(5) DEFAULT SYSDATE,

time2 DATETIME YEAR TO SECOND DEFAULT SYSDATE YEAR TO SECOND );

INSERT INTO tab1 VALUES (0, ‘description', SYSDATE, SYSDATE);

YEAR TO YEAR --年到年

YEAR TO MONTH --年到月

YEAR TO DAY --年到天

YEAR TO HOUR --年到时

YEAR TO MINUTE --年到分

YEAR TO SECOND --年到秒

YEAR TO FRACTION(5)--秒后的小数点位数

插入的小数点长度不一致会报上面错误

语法解释

The INTERVAL field qualifier specifies the precision, in time units, for an INTERVAL value. Use the INTERVAL Field Qualifier segment whenever you see a reference to an INTERVAL field qualifier in a syntax diagram.

Syntax

INTERVAL Field Qualifier

|--+-+-DAY--+-------------+--+-TO DAY---------------------+----+-+--|
   | |      '-(precision)-'  +-TO HOUR--------------------+    | |   
   | |                       +-TO MINUTE------------------+    | |   
   | |                       +-TO SECOND------------------+    | |   
   | |                       '-TO FRACTION--+-----------+-'    | |   
   | |                                      '-( scale )-'      | |   
   | +-HOUR--+-------------+--+-TO HOUR--------------------+---+ |   
   | |       '-(precision)-'  +-TO MINUTE------------------+   | |   
   | |                        +-TO SECOND------------------+   | |   
   | |                        '-TO FRACTION--+-----------+-'   | |   
   | |                                       '-( scale )-'     | |   
   | +-MINUTE--+-------------+--+-TO MINUTE------------------+-+ |   
   | |         '-(precision)-'  +-TO SECOND------------------+ | |   
   | |                          '-TO FRACTION--+-----------+-' | |   
   | |                                         '-( scale )-'   | |   
   | +-SECOND--+-------------+--+-TO SECOND---+----------------+ |   
   | |         '-(precision)-'  '-TO FRACTION-'                | |   
   | '-FRACTION--TO FRACTION--+-----------+--------------------' |   
   |                          '-( scale )-'                      |   
   '-+-YEAR--+-------------+--+-TO YEAR--+-+---------------------'   
     |       '-(precision)-'  '-TO MONTH-' |                         
     '-MONTH--+-------------+--TO MONTH----'                         
              '-(precision)-'                                        

Element	Description	Restrictions	Syntax
scale	Integer number of digits in FRACTION field. Default is 3.	Must be in the range from 1 to 5	Literal Number
precision	Integer number of digits in the largest time unit that the INTERVAL includes. For YEAR, the default is 4. For all other time units except FRACTION, the default is 2.	Must be in the range from 1 to 9	Literal Number

Usage

This segment specifies the precision and scale of an INTERVAL data type.

A keyword specifying the largest time unit must be the first keyword, and a keyword specifying the smallest time unit must follow the TO keyword. These can be the same keyword. This segment resembles the syntax of a DATETIME Field Qualifier, but with these exceptions:

• If the largest time unit keyword is YEAR or MONTH, the smallest time unit keyword cannot specify a time unit smaller than MONTH.
• You can specify up to 9-digit precision after the first time unit, unless FRACTION is the first time unit (in which case no precision is valid after the first FRACTION keyword, but you can specify up to 5 digits of scale after the second FRACTION keyword).

Because year and month are not fixed-length units of time, the database server treats INTERVAL data types that include the YEAR or MONTH keywords in their qualifiers as incompatible with INTERVAL data types whose qualifiers are time units smaller than MONTH. The database server supports no implicit casts between these two categories of INTERVAL data types.

The next two examples show YEAR TO MONTH qualifiers of INTERVAL data types. The first example can hold an interval of up to 999 years and 11 months, because it gives 3 as the precision of the YEAR field. The second example uses the default precision on the YEAR field, so it can hold an interval of up to 9,999 years and 11 months.

YEAR (3) TO MONTH

YEAR TO MONTH

When you want a value to specify only one kind of time unit, the first and last qualifiers are the same. For example, an interval of whole years is qualified as YEAR TO YEAR or YEAR (5) TO YEAR, for an interval of up to 99,999 years.

The following examples show several forms of INTERVAL field qualifiers:

YEAR(5) TO MONTH

DAY (5) TO FRACTION(2)

DAY TO DAY

FRACTION TO FRACTION (4)

For information about how to specify INTERVAL field qualifiers and how to use INTERVAL data in arithmetic and relational operations, see the related reference, INTERVAL data type.