AntDB-Oracle Compatibility Developer's Manual P3–4

Timestamp

A valid timestamp type input value is composed of a date and a time type value. The date part and the time part of the timestamp are capable of being formatted.

The following example of a timestamp uses Oracle's default format.

08-JAN-99 04:05:06

The following example of a timestamp follows the ISO 8601 standard format.

1999-01-08 04:05:06

Time interval

Interval is the interval before two dates or times.

INTERVAL YEAR TO MONTH：

*Format: *

INTERVAL 'integer \[- integer ]' { YEAR \| MONTH } \[ TO { YEAR \| MONTH } ]

INTERVAL 'integer \[- integer ]' { YEAR \| MONTH } \[ (precision) ]

Example:

postgres=#  select interval '100' year(2) from dual;
 INTERVAL  
------------------
       100 years
(1 row)

INTERVAL DAY TO SECOND

Format:

INTERVAL '{ integer \| integer time\_expr \| time\_expr }'

{{ DAY \| HOUR \| MINUTE \| SECOND }

\[ TO { DAY \| HOUR \| MINUTE \| SECOND \[ (fractional\_seconds\_precision) ] } ]

INTERVAL '{ integer \| integer time\_expr \| time\_expr }'

{ { DAY \| HOUR \| MINUTE \| SECOND} \[ (leading\_precision) ] }

Where precision means precision, in AntDB it doesn't actually limit the precision, but only supports it syntactically. In Oracle, if the specified precision is too small, it will report an error, but AntDB will not report an error, but still display the actual precision. And the precision of seconds can only be accurate to 6 digits. If the specified precision of seconds is greater than 6 bits, it will be converted to 6.

Example:

select INTERVAL '4 5:12:10.222' DAY TO SECOND(3) from dual;
select INTERVAL '4 5:12' DAY TO MINUTE  from dual;
select INTERVAL '400' DAY(3)  from dual;
select INTERVAL '11:12:10.2222222' HOUR TO SECOND(6)  from dual;
select INTERVAL '11:20' HOUR TO MINUTE  from dual;
select INTERVAL '10' HOUR  from dual;
select INTERVAL '10:22' MINUTE TO SECOND  from dual;
select INTERVAL '10' MINUTE  from dual;
select INTERVAL '4' DAY  from dual;
select INTERVAL '25' HOUR  from dual;
select INTERVAL '40' MINUTE  from dual;
select INTERVAL '120' HOUR(3) from dual; 
select INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND  from dual;

There is also incomplete support compared to Oracle, for example, the following formats are not supported:

select INTERVAL '400 5' DAY(3) TO HOUR from dual;
select INTERVAL '30.12345' SECOND(2,4)  from dual;
select INTERVAL '10:22' MINUTE(1) TO SECOND  from dual;

Date/time output format

The default output format for date/time types is the ISO 8601 format yyyy-mm-dd. In Oracle, the default output format is the format known as Redwood date (dd-MON-yy). However, in AntDB, if you want to output such a date format, you need to use the to_char function to format the output.

select to_char(sysdate,'DD-MON-MM hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD-MON-MM HH24:MI:SS')  
------------------------------------------
 03-SEP-09 15:51:47
(1 row)

The table below shows examples of the two styles of output formats, Redwood and ISO8601.

Description	Example
Redwood style	31-DEC-05 07:37:16
ISO 8601/SQL standard	1997-12-17 07:37:16

Boolean type

AntDB provides the standard SQL type BOOLEAN. BOOLEAN can only have one of two states: "true" or "false". The third state, "unknown", is represented by the null value in SQL language.