Help Center/ GaussDB/ Service Overview/ Compatibility with MySQL Databases/ Distributed Instances/ MySQL Compatibility in MySQL-Compatible Mode/ Data Types/ Date and Time Data Types
Updated on 2024-11-11 GMT+08:00
View PDF
Share

Date and Time Data Types

Table 1 Date and time data types

No.

MySQL

GaussDB

Difference

1

DATE

Supported.

GaussDB supports the date data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Input formats
    • GaussDB supports only the character type and does not support the numeric type. For example, the format can be '2020-01-01' or '20200101', but cannot be 20200101. MySQL supports conversion from numeric input to the date type.
    • Separator: GaussDB does not support the plus sign (+) or colon (:) as the separator between the year, month, and day. Other symbols are supported. MySQL supports all symbols as separators. Sometimes, the mixed use of separators is not supported, which is different from MySQL, such as '2020-01>01' and '2020/01+01'. You are advised to use hyphens (-) or slashes (/) as separators.
    • No separator: You are advised to use the complete format, for example, 'YYYYMMDD' or 'YYMMDD'. The parsing rules of incomplete formats (including the ultra-long format) are different from those of MySQL. An error may be reported or the parsing result may be inconsistent with that of MySQL. Therefore, the incomplete format is not recommended.
  • Output formats

    If the sql_mode parameter of GaussDB does not contain 'strict_trans_tables' (the strict mode is used unless otherwise defined as the loose mode), the year, month, and day can be set to 0. However, the value is converted to a valid value in the sequence of year, month, and day. For example, date '0000-00-10' is converted to 0002-12-10 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 is returned. MySQL outputs the date value as it is, even if the year, month, and day are set to 0.

  • Value ranges

    The value range of GaussDB is 4713-01-01 BC to 5874897-12-31 AD. BC dates are supported. In loose mode, if the value exceeds the range, 0000-00-00 is returned. In strict mode, an error is reported. The value range of MySQL is 0000-00-00 to 9999-12-31. In loose mode, if the value exceeds the range, the performance varies in different scenarios. An error may be reported (for example, in the SELECT statement) or the value 0000-00-00 may be returned (for example, in the INSERT statement). As a result, when the date type is used as the input parameter of the function, the results returned by the function are different.

  • Operators
    • GaussDB supports only the comparison operators =, !=, <, <=, >, and >= between date types and returns true or false. For the addition operation between the date and interval types, the return result is of the date type. For the subtraction operation between the date and interval types, the return result is of the date type. For the subtraction operation between date types, the return result is of the interval type.
    • When the MySQL date type and other numeric types are calculated, the date type is converted to the numeric type, and then the calculation is performed based on the numeric type. The result is also of the numeric type. It is different from GaussDB. For example:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    -- MySQL: date+numeric. Convert the date type to 20200101 and add it to 1. The result is 20200102.
mysql> select date'2020-01-01' + 1;
+----------------------+
| date'2020-01-01' + 1 |
+----------------------+
|             20200102 |
+----------------------+
1 row in set (0.00 sec)

-- GaussDB: date+numeric. Convert the numeric type to the interval type (1 day), and then add them up to obtain a new date.
gaussdb=# select date'2020-01-01' + 1;
  ?column?  
------------
 2020-01-02
(1 row)
  • Type conversion

    Compared with MySQL, GaussDB supports conversion between the date type and char(n), nchar(n), datetime, or timestamp type, but does not support conversion between the date type and binary, decimal, JSON, integer, unsigned integer, or time type. The principles for determining common types in scenarios such as collections and complex expressions are different from those in MySQL. For details, see Data Type Conversion.

2

DATETIME[(fsp)]

Supported.

GaussDB supports the datetime data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Input formats
    • GaussDB supports only the character type and does not support the numeric type. For example, '2020-01-01 10:20:30.123456' or '20200101102030.123456' is supported, but 20200101102030.123456 is not supported. MySQL supports conversion from numeric input to the datetime type.
    • Separator: GaussDB does not support the plus sign (+) or colon (:) as the separator between the year, month, and day. Other symbols are supported. Only colons (:) can be used as separators between hours, minutes, and seconds. Sometimes, the mixed use of separators is not supported, which is different from MySQL. Therefore, it is not recommended. MySQL supports all symbols as separators.
    • No separator: In GaussDB, the complete format 'YYYYMMDDhhmiss.ffffff' is recommended. The parsing rules of incomplete formats (including the ultra-long format) may be different from those of MySQL. An error may be reported or the parsing result may be inconsistent with that of MySQL. Therefore, the incomplete format is not recommended.
  • Output formats:
    • The format is 'YYYY-MM-DD hh:mi:ss.ffffff', which is the same as that of MySQL and is not affected by the DateStyle parameter. However, for the precision part, if the last several digits are 0, they are not displayed in GaussDB but displayed in MySQL.
    • If the sql_mode parameter of GaussDB does not contain 'strict_trans_tables' (the strict mode is used unless otherwise defined as the loose mode), the year, month, and day can be set to 0. However, the value is converted to a valid value in the sequence of year, month, and day. For example, datetime '0000-00-10 00:00:00' is converted to 0002-12-10 00:00:00 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 00:00:00 is returned. MySQL outputs the datetime value as it is, even if the year, month, and day are set to 0.
  • Value ranges

    4713-11-24 00:00:00.000000 BC to 294277-01-09 04:00:54.775806 AD. If the value is 294277-01-09 04:00:54.775807 AD, infinity is returned. If the value exceeds the range, GaussDB reports an error in strict mode. Whether MySQL reports an error depends on the application scenario. Generally, no error is reported in the query scenario. However, an error is reported when the DML or SQL statement is executed to change the value of a table attribute. In loose mode, GaussDB returns 0000-00-00 00:00:00. MySQL may report an error, return 0000-00-00 00:00:00, or return null based on the application scenario. As a result, the execution result of the function that uses the datetime type as the input parameter is different from that of MySQL.

  • Precision

    The value ranges from 0 to 6. For a table column, the default value is 0, which is the same as that in MySQL. In the datetime[(p)]'str' expression, GaussDB parses (p) as the precision. The default value is 6, indicating that 'str' is formatted to the datetime type based on the precision specified by p. MySQL does not support the datetime[(p)]'str' expression.

  • Operators
    • GaussDB supports only the comparison operators =, !=, <, <=, >, and >= between datetime types and returns true or false. For the addition operation between the datetime and interval types, the return result is of the datetime type. For the subtraction operation between the datetime and interval types, the return result is of the datetime type. For the subtraction operation between datetime types, the return result is of the interval type.
    • When the MySQL datetime type and other numeric types are calculated, the datetime type is converted to the numeric type, and then the calculation is performed based on the numeric type. The result is also of the numeric type. It is different from GaussDB. For example:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    -- MySQL: datetime+numeric. Convert the datetime type to 20201010123456 and add it to 1. The result is 20201010123457.
mysql> select cast('2020-10-10 12:34:56.123456' as datetime) + 1;
+----------------------------------------------------+
| cast('2020-10-10 12:34:56.123456' as datetime) + 1 |
+----------------------------------------------------+
|                                     20201010123457 |
+----------------------------------------------------+
1 row in set (0.00 sec)

-- GaussDB: datetime+numeric. Convert the numeric type to the interval type (1 day), and then add them up to obtain the new datetime.
gaussdb=# select cast('2020-10-10 12:34:56.123456' as datetime) + 1;
      ?column?       
---------------------
 2020-10-11 12:34:56
(1 row)

    If the calculation result of the datetime type and numeric type is used as the input parameter of a function, the result of the function may be different from that of MySQL.

  • Type conversion

    Compared with MySQL, GaussDB supports only conversion between the datetime type and char(n), varchar(n), and timestamp types, and conversion from datetime to date and time types (only value assignment and explicit conversion). The conversion between the datetime type and the binary, decimal, json, integer, or unsigned integer type is not supported. The principles for determining common types in scenarios such as collections and complex expressions are different from those in MySQL. For details, see Data Type Conversion.

  • Time zones

    In GaussDB, the datetime value can carry the time zone information (time zone offset or time zone name), for example, '2020-01-01 12:34:56.123456 +01:00' or '2020-01-01 2:34:56.123456 CST'. GaussDB converts the time to the time of the current server time zone. MySQL 5.7 does not support this function. MySQL 8.0 and later versions support this function.

  • The table columns of the datetime data type in GaussDB are actually converted to the timestamp(p) without time zone. When you query the table information or use a tool to export the table structure, the data type of columns is timestamp(p) without time zone instead of datetime. For MySQL, datetime(p) is displayed.

3

TIMESTAMP[(fsp)]

Supported.

GaussDB supports the timestamp data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Input formats:
    • It supports only the character type and does not support the numeric type. For example, '2020-01-01 10:20:30.123456' or '20200101102030.123456' is supported, but 20200101102030.123456 is not supported. MySQL supports conversion from numeric input to the timestamp type.
    • Separator: It does not support the plus sign (+) or colon (:) as the separator between the year, month, and day. Other symbols are supported. Only colons (:) can be used as separators between hours, minutes, and seconds. Sometimes, the mixed use of separators is not supported, which is different from MySQL. Therefore, it is not recommended. MySQL supports all symbols as separators.
    • No separator: The complete format 'YYYYMMDDhhmiss.ffffff' is recommended. The parsing rules of incomplete formats (including the ultra-long format) may be different from those of MySQL. An error may be reported or the parsing result may be inconsistent with that of MySQL. Therefore, the incomplete format is not recommended.
  • Output formats:
    • The format is 'YYYY-MM-DD hh:mi:ss.ffffff', which is the same as that of MySQL and is not affected by the DateStyle parameter. However, for the precision part, if the last several digits are 0, they are not displayed in GaussDB but displayed in MySQL.
    • If the sql_mode parameter of GaussDB does not contain 'strict_trans_tables' (the strict mode is used unless otherwise defined as the loose mode), the year, month, and day can be set to 0. However, the value is converted to a valid value in the sequence of year, month, and day. For example, timestamp '0000-00-10 00:00:00' is converted to 0002-12-10 00:00:00 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 00:00:00 is returned. MySQL outputs the timestamp value as it is, even if the year, month, and day are set to 0.
  • Value ranges

    4713-11-24 00:00:00.000000 BC to 294277-01-09 04:00:54.775806 AD. If the value is 294277-01-09 04:00:54.775807 AD, infinity is returned. If the value exceeds the range, GaussDB reports an error in strict mode. Whether MySQL reports an error depends on the application scenario. Generally, no error is reported in the query scenario. However, an error is reported when the DML or SQL statement is executed to change the value of a table attribute. In loose mode, GaussDB returns 0000-00-00 00:00:00. MySQL may report an error, return 0000-00-00 00:00:00, or return null based on the application scenario. As a result, the execution result of the function that uses the timestamp type as the input parameter is different from that of MySQL.

  • Precision:
    The value ranges from 0 to 6. For a table column, the default value is 0, which is the same as that in MySQL. In the timestamp[(p)] 'str' expression:
    • GaussDB parses (p) as the precision. The default value is 6, indicating that 'str' is formatted to the timestamp type based on the precision specified by p.
    • The meaning of timestamp 'str' in MySQL is the same as that in GaussDB. The default precision is 6. However, timestamp(p) 'str' is parsed as a function call. p is used as the input parameter of the timestamp function. The result returns a value of the timestamp type, and 'str' is used as the alias of the projection column.
  • Operators
    • GaussDB supports only the comparison operators =, !=, <, <=, >, and >= between timestamp types and returns true or false. For the addition operation between the timestamp and interval types, the return result is of the timestamp type. For the subtraction operation between the timestamp and interval types, the return result is of the timestamp type. For the subtraction operation between timestamp types, the return result is of the interval type.
    • When the MySQL timestamp type and other numeric types are calculated, the timestamp type is converted to the numeric type, and then the calculation is performed based on the numeric type. The result is also of the numeric type. It is different from GaussDB. For example:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    -- MySQL: timestamp+numeric. Convert the timestamp type to 20201010123456.123456 and add it to 1. The result is 20201010123457.123456.
mysql> select timestamp '2020-10-10 12:34:56.123456' + 1;
+--------------------------------------------+
| timestamp '2020-10-10 12:34:56.123456' + 1 |
+--------------------------------------------+
|                      20201010123457.123456 |
+--------------------------------------------+
1 row in set (0.00 sec)

-- GaussDB: timestamp+numeric. Convert the numeric type to the interval type (1 day), and then add them up to obtain a new timestamp.
gaussdb=# select timestamp '2020-10-10 12:34:56.123456' + 1;
          ?column?          
----------------------------
 2020-10-11 12:34:56.123456
(1 row)

    If the calculation result of the timestamp type and numeric type is used as the input parameter of a function, the result of the function may be different from that of MySQL.

  • Type conversion

    Compared with MySQL, GaussDB supports only conversion between timestamp and char(n), varchar(n), and datetime, and conversion from timestamp to date and time (only value assignment and explicit conversion). The conversion between the timestamp type and the binary, decimal, json, integer, or unsigned integer type is not supported. The principles for determining common types in scenarios such as collections and complex expressions are different from those in MySQL. For details, see Data Type Conversion.

  • Time zones

    In GaussDB, the timestamp value can carry the time zone information (time zone offset or time zone name), for example, '2020-01-01 12:34:56.123456 +01:00' or '2020-01-01 2:34:56.123456 CST'. GaussDB converts the time to the time of the current server time zone. If the time zone of the server is changed, the timestamp value is converted to the timestamp of the new time zone. MySQL 5.7 does not support this function. MySQL 8.0 and later versions support this function.

  • The table columns of the timestamp data type in GaussDB are actually converted to the timestamp(p) with time zone. When you query the table information or use a tool to export the table structure, the data type of columns is timestamp(p) with time zone instead of timestamp. For MySQL, timestamp(p) is displayed.

4

TIME[(fsp)]

Supported.

GaussDB supports the time data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Input formats:
    • It supports only the character type and does not support the numeric type. For example, '1 10:20:30' or '102030' is supported, but 102030 is not supported. MySQL supports conversion from numeric input to the time type.
    • Separator: GaussDB supports only colons (:) as separators between hours, minutes, and seconds. MySQL supports all symbols as separators.
    • No separator: The complete format 'hhmiss.ffffff' is recommended. The parsing rules of incomplete formats (including the ultra-long format) may be different from those of MySQL. An error may be reported or the parsing result may be inconsistent with that of MySQL. Therefore, the incomplete format is not recommended.
    • When a negative value is entered for minute, second, or precision, GaussDB may ignore the first part of the negative value, which is parsed as 0. For example, '00:00:-10' is parsed as '00:00:00'. An error may also be reported. For example, if '00:00:-10000' is parsed, an error will be reported. The result depends on the range of the input value. However, MySQL reports an error in both cases.
  • Output formats:

    The format is hh:mi:ss.ffffff, which is the same as that of MySQL. However, for the precision part, if the last several digits are 0, they are not displayed in GaussDB but displayed in MySQL.

  • Value ranges

    –838:59:59.000000 to 838:59:59.000000, which is the same as that of MySQL. In GaussDB loose mode, if a value exceeds the range, the nearest boundary value –838:59:59 or 838:59:59 is returned, regardless of the query or DML operations such as insert and update. In MySQL, an error is reported during query, or the nearest boundary value is returned after a DML operation. As a result, when the time type is used as the input parameter of the function, the results returned by the function are different.

  • Precision:

    The value ranges from 0 to 6. For a table column, the default value is 0, which is the same as that in MySQL. In the time(p) 'str' expression, GaussDB parses (p) as the precision. The default value is 6, indicating that 'str' is formatted to the time type based on the precision specified by p. MySQL parses it as a time function, p is an input parameter, and 'str' is the alias of the projection column.

  • Operators
    • GaussDB supports only the comparison operators =, !=, <, <=, >, and >= between time types and returns true or false. For the addition operation between the time and interval types, the return result is of the time type. For the subtraction operation between the time and interval types, the return result is of the time type. For the subtraction operation between time types, the return result is of the interval type.
    • When the MySQL time type and other numeric types are calculated, the time type is converted to the numeric type, and then the calculation is performed based on the numeric type. The result is also of the numeric type. It is different from GaussDB. For example:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    -- MySQL: time+numeric. Convert the time type to 123456 and add it to 1. The result is 123457.
mysql> select time '12:34:56' + 1;
+---------------------+
| time '12:34:56' + 1 |
+---------------------+
|              123457 |
+---------------------+
1 row in set (0.00 sec)

-- GaussDB: time+numeric. Convert the numeric type to the interval type (1 day), and then add them up to obtain the new time. Because 24 hours are added, the obtained time is still 12:34:56.
gaussdb=# select time '12:34:56' + 1;
 ?column? 
----------
 12:34:56
(1 row)

    If the calculation result of the time type and numeric type is used as the input parameter of a function, the result of the function may be different from that of MySQL.

  • Type conversion

    Compared with MySQL, GaussDB supports only conversion between the time type and char(n) or nchar(n) type, and conversion between the datetime or timestamp type and time type. The conversion between the time type and binary, decimal, date, JSON, integer, or unsigned integer type is not supported. The principles for determining common types in scenarios such as collections and complex expressions are different from those in MySQL. For details, see Data Type Conversion.

5

YEAR[(4)]

Supported.

GaussDB supports the year data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Operators
    • GaussDB supports only the comparison operators =, !=, <, <=, >, and >= between year types and returns true or false.
    • GaussDB supports only the arithmetic operators + and - between the year and int4 types and returns integer values. MySQL returns unsigned integer values.
  • Type conversion

    Compared with MySQL, GaussDB supports only the conversion between the year type and int4 type, and supports only the conversion from the int4, varchar, numeric, date, time, timestamp, or timestamptz type to the year type. The principles for determining common types in scenarios such as collections and complex expressions are different from those in MySQL. For details, see Data Type Conversion.

6

INTERVAL

Supported.

GaussDB supports the INTERVAL data type, but INTERVAL is an expression in MySQL. The differences are as follows:

  • The date input of the character string type cannot be used as an operation, for example, select '2023-01-01' + interval 1 day.
  • In the INTERVAL expr unit syntax, expr cannot be a negative integer or floating-point number, for example, select date'2023-01-01' + interval -1 day.
  • In the INTERVAL expr unit syntax, expr cannot be the input of an operation expression, for example, select date'2023-01-01' + interval 4/2 day.
  • When the INTERVAL expression is used for calculation, the return value is of the datetime type. For MySQL, the return value is of the datetime or date type. The calculation logic is the same as that of GaussDB but different from that of MySQL.
  • In the INTERVAL expr unit syntax, the value range of expr varies with the unit. The maximum value range is [–2147483648, 2147483647]. If the value exceeds the range, an error is reported in strict mode, and a warning is reported in loose mode and 0 is returned.
  • In the INTERVAL expr unit syntax, if the number of columns specified by expr is greater than the expected number of columns in unit, an error is reported in strict mode, and a warning is reported in loose mode and 0 is returned. For example, if the value of unit is DAY_HOUR, the expected number of columns is 2. If the value of expr is '1-2-3', the expected number of columns is 3.
Parent topic: Data Types