GBase 8a MPP Cluster Supported Data Types

Cong Li - Aug 27 - - Dev Community

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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!

Image
The Dos and Don'ts of Web Scraping

webscraping, proxy
Image
Living Large in a Small Space: The Ultimate Bachelor's Guide to Sofa Beds

sofabed, foldablesofa, foldingsofa
Image
Building an Effective Video Recommendation System: Problem Statement and Metrics
Image
Crypto News Digest: Today’s Top 5 Must-Read Headlines

cryptocurrency, beginners, web3
Image
Kanapy – Wygoda, Styl i Funkcjonalność w Twoim Salonie!
Image
RaspberryPiの初期セットアップ
Image
Individuell Rådgivning: Nyckeln till Personlig Utveckling och Framgång
Image
OracleCloudで自分だけのストレージと開発環境を構築する方法
Image
Gitのプロキシ設定を自動的に切り替える方法
Image
Math 4 Python 🧮 - Learn Python Through Math for Hacktoberfest 2024

devchallenge, hacktoberfestchallenge, hacktoberfest, opensource
Image
anyenvを用いてCleanなMac開発環境を構築する
Image
Kubernetes vs Docker: Understanding Container Orchestration and Management
Image
Garland, Cavaliers combat back again in opposition to 16 down in direction of overcome Hornets
Image
Avdija potential customers Washington from Denver following 43-position outing
Image
Simple hello world program using Bazel and Go lang

go, bazel, monorepo
Image
Liman MYS - Uygulama İzleme Ortamı Oluşturulması

liman, appmon, clickhouse, exporter
Image
Building a File Upload in FastAPI

fileupload, fastapi
Image
The Allure of Custom Clothing in a Fast-Fashion World
Image
Why Every Developer Should Understand OOP: A Complete Breakdown
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player