Abstract: This paper mainly introduces the behavior and comparison of the DATE type in GaussDB(DWS) data type in different database modes and different application scenarios.

This article is shared with “GaussDB(DWS) Data Type DATE Type” in Huawei cloud community. The original article is written by Xiaozhong.

GaussDB(DWS) has three modes: Oracle, TD, and MySQL. Different types have different behaviors in different modes. The DATE type in GaussDB(DWS) is described in detail.

The value range of the DATE type is as follows:

When GaussDB(DWS) stores the DATE type, the DATE type cannot be all or part of the year, month, and day. The value ranges are different in the three modes:

In Oracle mode, the DATE type ranges from January 1, 4713 BC to January 1, 294277 AD. In MySQL and TD modes, the DATE type ranges from 4713 BC to 5874897 AD.

Use case description:

1. Behavior of the DATE type in different modes

Oracle model

A. Tate type of performance

In GaussDB(DWS), the DATE type is stored without the time minute and second. In Oracle mode, the DATE type must contain the time minute and second. Therefore, GaussDB(DWS) has two DATE types in Oracle mode: with and without the time minute and second. Therefore, in order to distinguish easily, the type of DATE without time and seconds in display is represented by the column name DATE. A DATE type with a TIMESTAMP is represented by the column name of the same TIMESTAMP type with a TIMESTAMP.

postgres=# select date '4713-01-01 BC';
       timestamp        
------------------------
 4713-01-01 00:00:00 BC
(1 row)	
postgres=# select date '294277-01-01';
       timestamp       
-----------------------
 294277-01-01 00:00:00
(1 row)
Copy the code

B. In Oracle mode, get the DATE type method containing only year, month and day

If you want to convert a DATE to a DATE type in Oracle mode, is there a way to do this? In fact, there is. GaussDB(DWS) provides two methods. One is to use the date function in Oracle mode. The parameter of the date function must contain at least the year, month, and day. The other is to use the cast function, but the date should be quoted, otherwise the converted data will still contain the hour, minute and second part.

postgres=# select date('2008-05-24 10:40:21');
    date    
------------
 2008-05-24
(1 row)
postgres=# select cast('2008-05-24 10:40:21.100050' as "date");
    date    
------------
 2008-05-24
(1 row)
postgres=# select cast('2008-05-24 10:40:21.100050' as date);
      timestamp      
---------------------
 2008-05-24 10:40:21
(1 row)
Copy the code

C. Date type operator

MySQL mode and TD mode: The DATE type is only year, month and day.

A. DATE type

The DATE type in MySQL mode and TD mode is the same.

mysql_db=# select date '5874897-01-01';


     date     


---------------


 5874897-01-01


(1 row)


td_db=# select date '5874897-01-01';


     date     


---------------


 5874897-01-01


(1 row)
Copy the code

MySQL > create DATE (TD); MySQL > create DATE (TD)

In MySQL or TD mode, the DATE type has only year, month and day. Therefore, to_date and DATE are used to obtain only year, month and day. In cast mode, the result is the same with or without quotation marks.

td_db=# select date('2008-05-24 10:40:21'); Date -- -- -- -- -- -- -- -- -- -- -- -- the 2008-05-24 (row 1) td_db = # select cast (' 2008-05-24 10:40:21. 100050 'as "date"); Date -- -- -- -- -- -- -- -- -- -- -- -- the 2008-05-24 (row 1) td_db = # select cast (' 2008-05-24 10:40:21. 100050 'as the date); date ------------ 2008-05-24 (1 row) mysql_db=# select date('2008-05-24 10:40:21'); Date -- -- -- -- -- -- -- -- -- -- -- -- the 2008-05-24 (row 1) mysql_db = # select cast (' 2008-05-24 10:40:21. 100050 'as "date"); Date ------------ 2008-05-24 (1 row) mysql_db=# select cast('2008-05-24 10:40:21.100050' as date); date ------------ 2008-05-24 (1 row)Copy the code

C. DATE operator

Difference from Oracle mode: When subtracting values of type DATE, the return value does not contain day, but only number.

2. Behavior of DATE type related function results in different modes:

GaussDB(DWS) has functions related to the DATE type:

  • To_date: Converts a value of the text type to a timestamp of the specified format. If there is only one parameter, the default format is DATE.

  • To_char: Convert a DATETIME or INTERVAL value of the DATE, TIMESTAMP, TIMESTAMP WITHTIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE type to the format specified by FMT VARCHAR type. Optional FMT parameters can be of the following types: date, time, week, quarter, and century. Each class can have different templates, templates can be reasonably combined; The common templates are HH, MM, SS, YYYY, MM, and DD. Templates can have modifiers; A common modifier is FM, which can be used to suppress leading zeros or trailing whitespace. Return value type: vARCHAR

  • Current_date: Gets the current date.

  • Date_part: Gets the specified field value in the given date and time.

  • Isfinite: Tests whether the given date is a valid date.

Oracle mode: Because the DATE type in Oracle mode contains hours and seconds, the to_date and to_char results contain hours and seconds.

TD mode: In TD mode, the DATE type is year, month, and day. Therefore, the to_date function returns only year, month, and day. In TD mode, the output result of to_char can be controlled by GUC parameter convert_empty_STR_TO_NULl_TD. If no parameter is set, the result in the table above will be returned. If the parameter is set, the result will be the same as the result in TD database. For 2008/05/24.

MySQL: In MySQL mode, the DATE type is only year, month and day. Therefore, to_date and to_char return only year, month and day.

For more information about GuassDB(DWS), please search “GaussDB DWS” on wechat to follow the wechat public account, and share with you the latest and most complete PB series warehouse black technology, the background can also obtain many learning materials oh ~

Click to follow, the first time to learn about Huawei cloud fresh technology ~