Abstract:This paper mainly introduces the behavior and comparison of DATE type in GausSDB (DWS) data type under different database modes and different application scenarios.
This article is shared from the Huawei Cloud Community “Date Type of GausSDB (DWS) Data Type”, the original author: Xiao Zhong.
There are three modes in GausSDB (DWS), namely Oracle mode, TD mode and MySQL mode. Different types have different behaviors under different modes. Next, we’ll look at the Date type in GausSDB (DWS) in detail.
Date type range of values:
When storing DATE type, GausSDB (DWS) does not support the scenario where all or part of DATE is zero, and the range of values is different in the three modes:
In Oracle mode, the DATE type ranges from January 1, 4713 BC to January 1, 294277 AD. In the MySQL schema and TD schema, the DATE type ranges from 4713 BC to 5874897 AD.
Use case description:
1. Date type behavior in different modes
Oracle model
A. Representation of the Date type
Date type in GausSDB (DWS) is stored without time minutes and seconds. Since DATE type in Oracle mode needs time minutes and seconds, there are two types of DATE type in GausSDB (DWS) in Oracle mode, so in order to distinguish them easily, Date type without time minutes and seconds in display is represented by column name DATE; Date is represented by the column name TIMESTAMP of type TIMESTAMP, which is also 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)B. Get a date-type method containing only year, month and day
If you want to convert a DATE to a DATE with only year, month and day in Oracle mode, is there a way to do this? In fact, there is. GausSDB (DWS) provides two ways. One is by using the date function in Oracle mode. The date function takes a parameter containing at least year, month and day, and extracts and outputs the year, month and day from the date. The other is cast, but the date should be put in double quotation marks, otherwise the converted data will still contain the minutes and hours 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 is only year, month and day.
A. Representation of the Date type
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. MySQL = ‘DATE’; b. MySQL = ‘DATE’
Because in MySQL mode or TD mode, the DATE type is only year, month and day, the to_date function and cast function will produce the same result 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)C. Date type operator
Differences from Oracle schema: When subtracting values of type Date, the return value does not have day, only the numeric value.
2. Behavior of the result of the Date type correlation function in different modes:
There are functions related to the DATE type in GausSDB (DWS) :
- To_date: Converts a value of text type to a timestamp of the specified format. If there is only one argument, the default format is DATE type.
- To_char: CONVERT A DATETIME/INTERVAL VALUE OF DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, OR TIMESTAMP WITH LOCAL TIME ZONE TO VARCHAR (FMT). Optional parameters FMT can be in the following categories: date, time, week, quarter, and century. Each class can have a different template, between the template can be reasonable combination; Common templates are: HH, MM, SS, YYYY, MM, 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 if the given date is an effective date.
Oracle mode: Since the DATE type in Oracle mode contains minutes and seconds, the results from the to_date and to_char functions contain minutes and seconds.
TD mode: Since the DATE type in TD mode is only year, month and day, the to_date function returns only year, month and day. In TD mode, the output 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 table above is returned. If the parameter is set, the result is the same as that in the TD database, which is 2008/05/24.
MySQL mode: Since the DATE type in MySQL mode is year, month and day, the to_date and to_char functions return only year, month and day.
Want to know more information about GUASSDB (DWS), welcome WeChat search “GAUSSDB DWS” pay attention to WeChat public number, and you share the latest and most complete PB series silo black technology, background can also obtain many learning materials Oh ~
Click on the attention, the first time to understand Huawei cloud fresh technology ~