GBase 8a MPP Cluster Supported Data Types
Cong Li
Posted on August 27, 2024
Today, we'll introduce the date and time types supported by GBase 8a, as shown in the table below:
| Type Name | Minimum Value | Maximum Value | Format |
|---|---|---|---|
| DATE | 0001-01-01 | 9999-12-31 | YYYY-MM-DD |
| DATETIME | 0001-01-01 00:00:00.000000 | 9999-12-31 23:59:59.999999 | YYYY-MM-DD HH:MI:SS.ffffff |
| TIME | -838:59:59 | 838:59:59 | HHH:MI:SS |
| TIMESTAMP | 1970-01-01 08:00:01 | 2038-01-01 00:59:59 | YYYY-MM-DD HH:MI:SS |
When using date and time types, users should provide the correct format, such as YYYY-MM-DD or YYYY-MM-DD HH:MI:SS.
1. DATE
The DATE type supports a range from "0001-01-01" to "9999-12-31".
GBase 8a MPP Cluster displays DATE values in the "YYYY-MM-DD" format.
Examples:
Example 1: Insert a standard DATE value.
gbase> CREATE TABLE products (productDate DATE);
Query OK, 0 rows affected
gbase> INSERT INTO products(productDate) VALUES('2010-09-01');
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+-------------+
| productDate |
+-------------+
| 2010-09-01 |
+-------------+
1 row in set
Example 2: Insert a NULL value.
gbase> CREATE TABLE products (productDate DATE);
Query OK, 0 rows affected
gbase> INSERT INTO products(productDate) VALUES(NULL);
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+-------------+
| productDate |
+-------------+
| NULL |
+-------------+
1 rows in set
Example 3: Insert an invalid DATE value, and the system reports an error message.
gbase> CREATE TABLE products (productDate DATE);
Query OK, 0 rows affected
gbase> INSERT INTO products(productDate) VALUES('2010-09-31');
ERROR 1292 (22007): Incorrect date value: '2010-09-31' for column 'productDate' at row 1
2. TIME
GBase 8a MPP Cluster retrieves and displays TIME values as strings in the "HH:MM:SS" format (or "HHH:MM:SS" format).
The range for TIME is "-838:59:59" to "838:59:59". TIME type can be used not only to represent the time of day but also to indicate elapsed time or the interval between two events (which may be much greater than 24 hours or even negative).
For TIME values specified with string time delimiters, hours, minutes, or seconds less than 10 can be specified without two digits. "8:3:2" is equivalent to "08:03:02".
TIME values can be specified in various formats:
A string in the 'D HH:MM:SS.fraction' format. Any of the following "relaxed" syntaxes can be used: 'HH:MM:SS.fraction', 'HH:MM:SS', 'HH:MM', 'D HH:MM:SS', 'D HH:MM', 'D HH', or 'SS'. Here, D is a number of days between 0 and 34. Note that the fraction part can be precise to microseconds.
Examples:
Example 1: Insert a standard TIME value.
gbase> CREATE TABLE products (producttime TIME);
Query OK, 0 rows affected
gbase> INSERT INTO products(producttime) VALUES('12:35:23');
Query OK, 1 row affected
gbase> SELECT producttime FROM products;
+-------------+
| producttime |
+-------------+
| 12:35:23 |
+-------------+
1 row in set
Example 2: Insert a NULL value.
gbase> CREATE TABLE products (producttime TIME);
Query OK, 0 rows affected
gbase> INSERT INTO products(producttime) VALUES(NULL);
Query OK, 1 row affected
gbase> SELECT producttime FROM products;
+-------------+
| producttime |
+-------------+
| NULL |
+-------------+
1 rows in set
Example 3: Insert an invalid TIME value, and the system reports an error message.
gbase> CREATE TABLE products (producttime TIME);
Query OK, 0 rows affected
gbase> INSERT INTO products(producttime) VALUES('14:08:89');
ERROR 1292 (22007): Incorrect time value: '14:08:89' for column 'producttime' at row 1
3. DATETIME
GBase 8a MPP Cluster displays DATETIME values in the "YYYY-MM-DD HH:MI:SS.fraction" format, where fraction represents the microsecond format, supporting up to 6 digits.
The combined date and time type supports a range from "0001-01-01 00:00:00.000000" to "9999-12-31 23:59:59.999999".
Examples:
Example 1: Insert a valid DATETIME value.
gbase> CREATE TABLE products (productDate DATETIME);
Query OK, 0 rows affected
gbase> INSERT INTO products(productDate) VALUES('2010-09-01 12:09:44');
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+---------------------+
| productDate |
+---------------------+
| 2010-09-01 12:09:44 |
+---------------------+
1 row in set
Example 2: Insert the current system DATETIME value.
gbase> INSERT INTO products(productDate) VALUES(NOW());
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+---------------------+
| productDate |
+---------------------+
| 2013-10-16 17:51:38 |
+---------------------+
1 row in set
Example 3: Insert a NULL value.
gbase> INSERT INTO products(productDate) VALUES(NULL);
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+-------------+
| productDate |
+-------------+
| NULL |
+-------------+
1 row in set
Example 4: Insert a DATETIME value with microseconds.
gbase> INSERT INTO products(productDate) VALUES('2013-09-15 12:09:44.123456');
Query OK, 1 row affected
gbase> SELECT productDate FROM products;
+----------------------------+
| productDate |
+----------------------------+
| 2013-09-15 12:09:44.123456 |
+----------------------------+
1 row in set
Example 5: Insert an invalid DATETIME value, and the system will report an error message.
gbase> INSERT INTO products(productDate) VALUES('2010-09-31 12:09:44');
ERROR 1292 (22007): Incorrect datetime value: '2010-09-31 12:09:44' for column 'productDate' at row 1
4. TIMESTAMP
The TIMESTAMP type is only provided for SQL standard compatibility. It is recommended to use the DATETIME data type instead.
The format for TIMESTAMP is "YYYY-MM-DD HH:MI:SS", with a supported range from "1970-01-01 08:00:01" to "2038-01-01 00:59:59".
To enable support for microsecond precision with TIMESTAMP, you need to enable the parameter: _gbase_timestamp_append_prec.
- The default value is OFF (0), where TIMESTAMP precision supports only up to seconds. If the inserted data contains microseconds, only seconds are saved, and microseconds are discarded, making them non-queryable.
- When set to 1, microsecond precision is enabled, supporting up to 6 digits (0~999999). If the data contains fewer than 6 digits, zeros will be added to complete it to 6 digits.
After enabling microsecond precision for TIMESTAMP, all DQL, DML, insert-select operations, and import/export operations support six-digit precision types. Precision can be preserved for data with precision when using DBLink to access remote data tables. Microsecond precision is also supported in the following time functions: current_timestamp, localtime, localtimestamp, now, and sysdate.
gbase> CREATE TABLE t (a int,b timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c timestamp DEFAULT '2013-01-01 00:00:01');
Query OK, 0 rows affected
gbase> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------+
| t | CREATE TABLE "t" (
"a" int(11) DEFAULT NULL,
"b" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
"c" timestamp NOT NULL DEFAULT '2013-01-01 00:00:01'
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+----------------------------------------------------------------+
1 row in set
gbase> show variables like '_gbase_timestamp_append%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| _gbase_timestamp_append_prec | OFF |
+------------------------------+-------+
gbase> create table t(a int,b timestamp);
Query OK, 0 rows affected (Elapsed: 00:00:02.19)
gbase> insert into t values(1,now());
Query OK, 1 row affected (Elapsed: 00:00:00.86)
gbase> insert into t values(2,'2021-3-4 12:12:12.123456');
Query OK, 1 row affected (Elapsed: 00:00:00.17)
gbase> select * from t;
+------+---------------------+
| a | b |
+------+---------------------+
| 1 | 2021-04-05 14:42:41 |
| 2 | 2021-03-04 12:12:12 |
+------+---------------------+
2 rows in set (Elapsed: 00:00:01.11)
gbase> show variables like '%_gbase_timestamp_append_prec%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| _gbase_timestamp_append_prec | ON |
+------------------------------+-------+
gbase> update t set b='2021-03-04 15:12:12.123';
Query OK, 4 rows affected (Elapsed: 00:00:00.07)
Rows matched: 4 Changed: 4 Warnings: 0
gbase> select * from t;
+------+----------------------------+
| a | b |
+------+----------------------------+
| 1 | 2021-03-04 15:12:12.123000 |
| 2 | 2021-03-04 15:12:12.123000 |
| 3 | 2021-03-04 15:12:12.123000 |
| 4 | 2021-03-04 15:12:12.123000 |
+------+----------------------------+
4 rows in set (Elapsed: 00:00:00.03)
gbase> insert into t values(5,now());
Query OK, 1 row affected (Elapsed: 00:00:00.41)
gbase> select * from t;
+------+----------------------------+
| a | b |
+------+----------------------------+
| 1 | 2021-03-04 15:12:12.123000 |
| 2 | 2021-03-04 15:12:12.123000 |
| 3 | 2021-03-04 15:12:12.123000 |
| 4 | 2021-03-04 15:12:12.123000 |
| 5 | 2021-04-05 14:55:49 |
+------+----------------------------+
5 rows in set (Elapsed: 00:00:00.02)
gbase> insert into t values(6,now(3));
Query OK, 1 row affected (Elapsed: 00:00:00.34)
gbase> select * from t;
+------+----------------------------+
| a | b |
+------+----------------------------+
| 1 | 2021-03-04 15:12:12.123000 |
| 2 | 2021-03-04 15:12:12.123000 |
| 3 | 2021-03-04 15:12:12.123000 |
| 4 | 2021-03-04 15:12:12.123000 |
| 5 | 2021-04-05 14:55:49 |
| 6 | 2021-04-05 14:56:03.737000 |
+------+----------------------------+
6 rows in set (Elapsed: 00:00:00.03)
gbase> insert into t values(7,now(6));
Query OK, 1 row affected (Elapsed: 00:00:00.07)
gbase> select * from t;
+------+----------------------------+
| a | b |
+------+----------------------------+
| 1 | 2021-03-04 15:12:12.123000 |
| 2 | 2021-03-04 15:12:12.123000 |
| 3 | 2021-03-04 15:12:12.123000 |
| 4 | 2021-03-04 15:12:12.123000 |
| 5 | 2021-04-05 14:55:49 |
| 6 | 2021-04-05 14:56:03.737000 |
| 7 | 2021-04-05 14:56:18.279518 |
+------+----------------------------+
7 rows in set (Elapsed: 00:00:00.02)
TIMESTAMP Usage Restrictions
The following restrictions apply to scenarios where TIMESTAMP data columns are automatically updated:
When using the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP property, TIMESTAMP columns are automatically updated during INSERT, UPDATE, and MERGE operations. When creating a table, one or more TIMESTAMP columns can be defined. If only one column is defined, the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP property can be omitted during creation, as the system will add it automatically. If multiple columns are defined, the first TIMESTAMP column must specify the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP property, while the other TIMESTAMP columns must not specify this property.
Note:
The parameter _gbase_timestamp_append_prec is read-only, and once set to 1, it cannot be reverted to 0. Modifying this parameter requires updating the configuration files on all nodes and restarting the service to take effect. It is recommended to add this parameter under the [gbased] tag in gcluster/config/gbase_8a_gcluster.cnf and gnode/config/gbase_8a_gbase.cnf, as shown below:
_gbase_timestamp_append_prec=1
GSSYS tables and system tables are not controlled by this parameter. It has not been tested for standalone development, so standalone use should be approached with caution.
That's all for today's content. Thank you for reading!
Posted on August 27, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
