Date/Time Type

For details about the date/time types supported by DataArts Fabric SQL, see Table 1. For information on the operators and built-in functions for these types, see Time and Date Processing Functions and Operators.

If the time format of another database differs from that of DataArts Fabric SQL, adjust the configuration parameter DateStyle to ensure consistency.

**Table 1** Date/Time types
Type	Description	Storage Space
DATE	In Oracle compatibility mode, it is equivalent to timestamp(0) and records both date and time. It records only the date in other modes.	8 bytes in Oracle compatibility mode. 4 bytes in other modes.
TIMESTAMP[(p)] [WITHOUT TIME ZONE]	Date and time. p indicates precision after the decimal point. The value ranges from 0 to 6.	8 bytes

Example:

   
    
      
      
        -- Create a table.
CREATE TABLE date_type_t1(
DA_COL1 DATE
) STORE AS orc;

-- Insert data.
INSERT INTO date_type_t1 VALUES (date '12-10-2010');

-- View data.
SELECT * FROM date_type_t1;
  da_col1   
------------
 2010-12-10
(1 row)

-- Drop the table.
DROP TABLE date_type_t1;

-- Create a table.
CREATE TABLE timestamp_type_t1
(
TS_COL1 TIMESTAMP,
TS_COL2 TIMESTAMP(1) WITHOUT TIME ZONE,
TS_COL3 TIMESTAMP(6) WITHOUT TIME ZONE
) STORE AS orc;
-- Insert data.
INSERT INTO timestamp_type_t1 VALUES ('2024-11-15 21:21:21.2565455', '2024-11-15 21:21:21.2565455', '2024-11-15 21:21:21.2565455');
-- View data.
SELECT * FROM timestamp_type_t1;
          ts_col1           |          ts_col2           |          ts_col3           
----------------------------+----------------------------+----------------------------
 2024-11-15 21:21:21.256545 | 2024-11-15 21:21:21.256545 | 2024-11-15 21:21:21.256545
(1 row)

-- Drop the table.
DROP TABLE timestamp_type_t1;

       

     

   
  

Date Input

Dates and times can be entered in nearly any reasonable format, including ISO-8601, SQL-compatible, traditional POSTGRES, or others. The system allows you to customize the order of day, month, and year for date inputs. Setting the DateStyle parameter to MDY parses dates as month-day-year, DMY as day-month-year, and YMD as year-month-day.

Textual date entries must be enclosed in single quotes, with the following syntax:

type [ ( p ) ] 'value'

Here, p in the optional precision declaration is an integer indicating the fractional seconds digits. Table 2 illustrates the input methods for the date type.

**Table 2** Date input methods
Example	Description
1999-01-08	ISO 8601 format (preferred), always interpreted as January 8, 1999.
January 8, 1999	Unambiguous under any datestyle input mode.
1/8/1999	Ambiguous. Interpreted as January 8 in MDY mode, August 1 in DMY mode.
1/18/1999	Interpreted as January 18 in MDY mode, rejected otherwise.
01/02/03	January 2, 2003 in MDY mode. February 1, 2003 in DMY mode. February 3, 2001 in YMD mode.
1999-Jan-08	Always January 8 regardless of mode.
Jan-08-1999	Always January 8 regardless of mode.
08-Jan-1999	Always January 8 regardless of mode.
99-Jan-08	January 8 in YMD mode, else an error.
08-Jan-99	January 8, except erroneous in YMD mode.
Jan-08-99	January 8, except erroneous in YMD mode.
19990108	ISO 8601. Always January 8, 1999.
990108	ISO 8601. Always January 8, 1999.
1999.008	Year and day within the year.
J2451187	Julian day.
January 8, 99 BC	99 BC.

Example:

    
     
       
       
         -- Create a table.
CREATE TABLE date_style_t1(DAT_COL1 DATE) STORE AS orc;

-- Insert data.
INSERT INTO date_style_t1 VALUES(date '12-10-2010');

-- View data.
SELECT  * FROM date_style_t1;
      dat_col1       
---------------------
 2010-12-10 00:00:00
(1 row)

-- View the date format.
SHOW datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

-- Set the date format.
SET datestyle='YMD';
SET

-- Insert data.
INSERT INTO date_style_t1 VALUES(date '2010-12-11');

-- View data.
SELECT  * FROM date_style_t1;
      dat_col1       
---------------------
 2010-12-10 
 2010-12-11 
(2 rows)

-- Drop the table.
DROP TABLE date_style_t1;

        

      

    
   

Time

The time type supports timestamp [ (p) ] without time zone. If only timestamp is written, it is equivalent to timestamp without time zone.

If a time zone is specified in the input of the time without time zone type, the time zone is ignored.

For details about time input types, refer to Table 3. For details about time zone input types, refer to Table 4.

**Table 3** Time input
Example	Description
05:06.8	ISO 8601
4:05:06	ISO 8601
4:05	ISO 8601
40506	ISO 8601
4:05 AM	Same as 04:05. AM does not affect the value.
4:05 PM	Same as 16:05. The input hour must be <= 12.
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601
04:05:06 PST	Abbreviated time zone.

**Table 4** Time zone input
Example	Description
PST	Abbreviation (for Pacific Standard Time).
-8:00	Offset between ISO 8601 and PST.
-800	Offset between ISO 8601 and PST.
-8	Offset between ISO 8601 and PST.

Special Values

DataArts Fabric SQL supports several special values, which will be converted into regular date/time values upon reading. Refer to Table 5 for more information.

**Table 5** Special values
Input String	Applicable Type	Description
epoch	date, timestamp	1970-01-01 00:00:00+00 (Unix epoch)
infinity	timestamp	Later than any other timestamp
-infinity	timestamp	Earlier than any other timestamp
now	date, time, timestamp	Start time of the current transaction
today	date, timestamp	Today's midnight
tomorrow	date, timestamp	Tomorrow's midnight
yesterday	date, timestamp	Yesterday's midnight
allballs	time	00:00:00.00 UTC