M*-Compatible Time Types

Compared with the original MogDB, Dolphin modifies the date/time types as follows:

1. The performance of the date, time, datetime, and timestamp types are modified.
2. The year data type is added.

Note: Due to the inherent features of the MogDB, the MogDB cannot be fully compatible with all features of the M* time data type. Therefore, you need to use the features according to the requirements in this document. Do not use the features that are not described in this document. In addition, the compatible features cover the requirements in most scenarios.

The following table lists the basic attributes after the time data type is compatible with the M* database.

Remarks

• Note that, for M*, in the CREATE TABLE or ALTER TABLE statement, if the precision is not specified when the time type (such as timestamp, datetime, and time) column attribute is defined, the default value 0 is used. When the cast(expr as typename) syntax is used for type conversion, if no precision is specified for the target type, the default precision is 0. Therefore, if you want to retain the input precision of the data, you need to explicitly use typmod.
• In addition, :: is used to convert the compatible time type. If no precision is specified for the target type, the default precision is 0.

Date Type Inputs

The following formats are supported:

Remarks:

• The input must be a valid date. The month or date cannot be 0.
• The original value range of M* is within 10000. Therefore, if you want to enter a date greater than or equal to 10000, use the YYYY-MM-DD format, for example, '10100-12-12'.
• Year 0000 is allowed. In addition, in MogDB, year 0000 is considered a leap year. You can enter 0000-2-29 (M* not allowed).

Examples (Note that MogDB is compatible with B database.)

--Create a table.
MogDB=# CREATE TABLE test_date(
MogDB(# dt date);
CREATE TABLE
    
--Insert data.
MogDB=# INSERT INTO test_date VALUES ('2020-12-21');
INSERT 0 1
MogDB=# INSERT INTO test_date VALUES ('141221');
INSERT 0 1
MogDB=# INSERT INTO test_date VALUES (20151022);
INSERT 0 1

--View data.
MogDB=# SELECT * FROM test_date;
     dt     
------------
 2020-12-21
 2014-12-21
 2015-10-22
(3 rows)

Time Type Inputs

The following formats are supported:

Remarks:

• For the format 'hh:mm:ss', the loose input formats 'hh:mm' and 'ss' are also supported.
• When the integer 0 is entered, the value is 00:00:00, which is also a zero value of the time type.
• After the time type is compatible, the value range can be greater than 24 hours. Do not convert the time type to the timetz type.

Examples (Note that MogDB is compatible with B database.)

--Create a table.
MogDB=# CREATE TABLE test_time(
MogDB(# ti time(2));
CREATE TABLE
    
--Insert data.
MogDB=# INSERT INTO test_time VALUES ('2 9:12:24.1234');
INSERT 0 1
MogDB=# INSERT INTO test_time VALUES ('-34:56:59.1234');
INSERT 0 1
MogDB=# INSERT INTO test_time VALUES (561234);
INSERT 0 1

--View data.
MogDB=# SELECT * FROM test_time;
      ti      
--------------
 57:12:24.12
 -34:56:59.12
 56:12:34
(3 rows)

Datetime Type Inputs

The following formats are supported:

Remarks:

• The input must be a valid date. The month or date cannot be 0.
• For the YYYYMMDDhhmmss and YYMMDDhhmmss formats, the first four letters of the character string are identified as the year only when the length of the character string is 8 or 14. In other cases, only the first two letters are identified as the year.
• If the input format is YYYYMMDDhhmmss or YYMMDDhhmmss, the length of the input integer must be 6, 8, 12, or 14. If the length does not meet this requirement, zeros are added before the integer, if the length is 6, 8, 12, or 14, the value is in the YYMMDD format. If the length is 8, the value is in the YYYYMMDD format. If the length is 12, the value is in the YYMMDDhhmmss format. If the length is 14, the value is in the YYYYMMDDhhmmss format.
• If you want to enter a timestamp whose year is greater than or equal to 10000, use the 'YYYY-MM-DD hh:mm:ss[.frac]' format.

Examples (Note that MogDB is compatible with B database.)

--Create a table.
MogDB=# CREATE TABLE test_datetime(
MogDB(# dt datetime(2));
CREATE TABLE
    
--Insert data.
MogDB=# INSERT INTO test_datetime VALUES ('2020-11-08 02:31:25.961');
INSERT 0 1
MogDB=# INSERT INTO test_datetime VALUES (201112234512);
INSERT 0 1

--View data.
MogDB=# SELECT * FROM test_datetime;
           dt           
------------------------
 2020-11-08 02:31:25.96
 2020-11-12 23:45:12
(3 rows)

Timestamp Type Inputs

The following formats are supported:

Remarks:

• The input must be a valid date. The month or date cannot be 0.
• The compatible timestamp type allows the time zone information [+/-hh:mm:ss] to be added after the format 'YYYY-MM-DD hh:mm:ss[.frac]'.
• If you want to enter a timestamp whose year is greater than or equal to 10000, use the 'YYYY-MM-DD hh:mm:ss[.frac]' format.
• Note that the timestamp type in the M* database is a timestamp without the time zone, and that in MogDB is a timestamp with the time zone. After compatibility, the timestamp type is stored in the timestamptz type internally. Note that the timestamp type is stored in the timestamptz type. Pay attention to the difference before using the timestamp type. If you want to use a timestamp without a time zone, use the datetime type.
• Note: The M* does not have the timestamp with[out] time zone syntax, but we still retain this syntax in MogDB. The timestamp with time zone is equivalent to the original MogDB timestamptz type, and timestamp without time zone is equivalent to the timestamp type in the original MogDB (not the compatible timestamp type, that is, the original timestamp without time zone).

Examples (Note that MogDB is compatible with B database.)

--Create a table.
MogDB=# CREATE TABLE test_timestamp(
MogDB(# ts timestamp(2));
CREATE TABLE
    
--Insert data.
MogDB=# INSERT INTO test_timestamp VALUES ('2012-10-21 23:55:23-12:12');
INSERT 0 1
MogDB=# INSERT INTO test_timestamp VALUES (201112234512);
INSERT 0 1

--View data.
MogDB=# SELECT * FROM test_timestamp;
           ts           
------------------------
 2012-10-22 20:07:23
 2020-11-12 23:45:12
(3 rows)
    
--Change the time zone.
MogDB=# SET TIME ZONE UTC;
SET
--View data.
MogDB=# SELECT * FROM test_timestamp;
           ts           
------------------------
 2012-10-22 12:07:23
 2020-11-12 15:45:12
(3 rows)

Input of the year/year(4) or year(2) type

The following formats are supported:

Remarks

• The three types accept the same input format and range. The only difference is that the output format of the year(2) type is only two digits.
• If '0' is entered, MogDB will parse the value to the year 2000. However, if an integer 0 is entered, MogDB will parse the value to 0, indicating the value 0 of the year type.

Examples (Note that MogDB is compatible with B database.)

--Create a table.
MogDB=# CREATE TABLE test_year(
MogDB(#  y year,
MogDB(#  y2 year(2));
CREATE TABLE
    
--Insert data.
MogDB=# INSERT INTO test_year VALUES ('70', '70');
INSERT 0 1
MogDB=# INSERT INTO test_year VALUES ('69', '69');
INSERT 0 1
MogDB=# INSERT INTO test_year VALUES ('2069', '2069');
INSERT 0 1
MogDB=# INSERT INTO test_year VALUES ('1970', '1970');
INSERT 0 1

--View data.
MogDB=# SELECT * FROM test_year;
  y   | y2 
------+----
 1970 | 70
 2069 | 69
 2069 | 69
 1970 | 70
(4 rows)