Behavior analysis of DATE type in different database modes

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

This article is shared from the HUAWEI CLOUD community " GaussDB (DWS) data type DATE type ", the original author: Xiaozhong.

There are three modes in GaussDB (DWS), namely Oracle mode, TD mode and MySQL mode. Different types have different behaviors in different modes. Next, the DATE type in GaussDB (DWS) will be introduced in detail.

Value range of DATE type:

When GaussDB (DWS) stores the DATE type, it does not support scenarios where the year, month and day are all zero or part of zero, and the value range is different in the three modes:

In Oracle mode, the value range of the DATE type is January 1, 4713 BC to January 1, 294277 AD. In MySQL mode and TD mode, the value range of the DATE type is 4713 BC to 5874897 AD.

Use case description:

1. DATE type behavior in different modes

Oracle mode

a. DATE type performance

The DATE type in GaussDB (DWS) is stored without hours, minutes and seconds. Because the DATE type in Oracle mode needs to have hours, minutes and seconds, there are two types of GaussDB (DWS) with hours, minutes and seconds and without hours, minutes and seconds in Oracle mode. DATE type, so for easy distinction, the DATE type without hours, minutes and seconds when displayed is represented by the column name date; the DATE type with hours, minutes and seconds is represented by the column name timestamp of the TIMESTAMP type with the same time, minutes and seconds.

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)

b. In Oracle mode, get the DATE type method

If you want to convert a date into a DATE type with only year, month and day in Oracle mode, is there a way to achieve it? In fact, there are. GaussDB (DWS) provides two methods. One is to use the date function in Oracle mode: the parameters of the date function must contain at least the year, month, and day, and extract and output the year, month, and day in the date; the other is Use the cast function to convert, but the date should be enclosed in double quotes when converting, 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)

c. DATE type operator

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

a. DATE type performance

There is no difference between the DATE type in MySQL mode and TD mode.

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)

b. In MySQL mode and TD mode, the method to get the DATE type containing only the year, month, and day

Since in MySQL mode or TD mode, the DATE type has only year, month and day, the results obtained by using the to_date function and the date function are only year, month and day. At the same time, when the cast function performs type conversion, the result obtained by adding or not adding quotation marks to date It is also consistent.

td_db=# select date('2008-05-24 10:40:21');

    date   

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

 2008-05-24

(1 row)

td_db=# select cast('2008-05-24 10:40:21.100050' as "date");

    date   

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

 2008-05-24

(1 row)

td_db=# select cast('2008-05-24 10:40:21.100050' as date);

    date   

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

 2008-05-24

(1 row)



mysql_db=# select date('2008-05-24 10:40:21');

    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)

mysql_db=# select cast('2008-05-24 10:40:21.100050' as date);

    date   

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

 2008-05-24

(1 row)

c. DATE type operator

The difference with the Oracle mode: When the DATE type value is subtracted, the return value does not carry the day, but only the value.

2. The behavior of the DATE type related function results in different modes:

There are functions related to the DATE type in GaussDB (DWS):

• : converts the value of text type to a timestamp in the specified format. If there is only one parameter, the default format is DATE type format.
• to_char: converts a DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE type DATETIME or INTERVAL value into a VARCHAR type according to the format specified by fmt. The optional parameter fmt can be of the following types: date, time, week, quarter, and century. Each category can have different templates, and the templates can be reasonably combined; common templates are: HH, MM, SS, YYYY, MM, DD. Templates can have modifiers; the commonly used modifier is FM, which can be used to suppress leading zeros or trailing blanks. Return value type: varchar
• current_date: get 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 the Oracle mode contains hours, minutes and seconds, the results obtained by the to_date and to_char functions contain hours, minutes and seconds.

TD mode: Because the DATE type in TD mode only has year, month and day, the result of to_date function is only year, month and day. In the TD mode, the output result of the to_char function can be controlled by the GUC parameter convert_empty_str_to_null_td. If the parameter is not set, the result in the above table is returned. If the parameter is set, the returned result is consistent with the result in the TD database, which is 2008/05/24 .

MySQL mode: Since the DATE type in MySQL mode only has year, month and day, the return results of to_date and to_char functions are only year, month and day.

If you want to know more about GuassDB (DWS), welcome to search "GaussDB DWS" on WeChat and follow the WeChat official account, and share with you the latest and most complete PB-level digital warehouse black technology. You can also get a lot of learning materials in the background~

Click to follow and learn about Huawei Cloud's fresh technology for the first time~