Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ Data Types/ Date and Time Types
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Date and Time Types

Table 1 lists the date and time types that can be used in M-compatible databases.

Table 1 Date and Time Types

Name

Description

Storage Space

DATE

Used to store year, month, and day information, that is, date information.

  • Input format: Strings 'YYYY-MM-DD' with separators and 'YYYYMMDD' without separators as well as numbers YYYYMMDD are supported. A year can be specified with only two digits. In this case, 70–99 corresponds to 1970–1999 and 00–69 corresponds to 2000–2069 by default.
    • Any punctuation character can be used as the separator.
  • Output format: Only YYYY-MM-DD is supported.
  • Value range: 1000-01-01 to 9999-12-31.
    • If the value of sql_mode contains allow_invalid_dates, the month in the range from 1 to 12 and the day in the range from 1 to 31 can be arbitrarily combined. That is, 02-31 can also be stored.
    • If the value of sql_mode does not contain no_zero_in_date, the month and day can be 0. That is, 1999-00-00 is legal.
    • If the value of sql_mode does not contain no_zero_date, the year, month and day can be 0. That is, 0000-00-00 is legal.

4 bytes

TIME[(p)]

Used to store hour, minute, second, and millisecond information, that is, time information.

  • Input format: Strings 'hh:mm:ss' with separators and 'hhmmss' without separators as well as numbers hhmmss are supported. Strings 'D hh:mm:ss' containing the day information is supported. Milliseconds are supported. The input value is rounded off to the specified precision.
    • Only a single colon (:) can be used as the separator.
  • Output format: hh:mm:ss or hhh:mm:ss is supported.
  • Value range: –838:59:59.000000 to 838:59:59.000000.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If p is set to a value greater than 6, the value 6 is used.

8 bytes

DATETIME[(p)]

Used to store year, month, and day information, that is, date information.

  • Input format: Strings 'YYYY-MM-DD' with separators and 'YYYYMMDD' without separators as well as numbers YYYYMMDD are supported. A year can be specified with only two digits. In this case, 70–99 corresponds to 1970–1999 and 00–69 corresponds to 2000–2069 by default. Milliseconds are supported. The input value is rounded off to the specified precision.
    • Any punctuation character can be used as the separator in the date or time part while only the decimal point can be used as the separator before milliseconds.
    • Spaces or a single uppercase letter T is allowed between the date part (year, month, and day) and the time part (hour, minute, and second). Spaces apply only to strings with separators.
  • Output format: Only YYYY-MM-DD hh:mm:ss is supported.
  • Value range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
    • If the value of sql_mode contains allow_invalid_dates, the month in the range from 1 to 12 and the day in the range from 1 to 31 can be arbitrarily combined. That is, 02-31 can also be stored.
    • If the value of sql_mode does not contain no_zero_in_date, the month and day can be 0. That is, 1999-00-00 10:00:00 is legal.
    • If the value of sql_mode does not contain no_zero_date, the year, month and day can be 0. That is, 0000-00-00 10:00:00 is legal.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If p is set to a value greater than 6, the value 6 is used.

8 bytes

TIMESTAMP[(p)]

Used to store data that contains both date and time information. Values are affected by the database time zone.

  • Input format: Strings 'YYYY-MM-DD hh:mm:ss' with separators and 'YYYYMMDDhhmmss' without separators as well as numbers YYYYMMDDhhmmss are supported. A year can be specified with only two digits. In this case, 70–99 corresponds to 1970–1999 and 00–69 corresponds to 2000–2069 by default. The input value is rounded off to the specified precision.
    • Any punctuation character can be used as the separator in the date or time part while only the decimal point can be used as the separator before milliseconds.
    • Spaces or a single uppercase letter T is allowed between the date part (year, month, and day) and the time part (hour, minute, and second). Spaces apply only to strings with separators.
  • Output format: Only YYYY-MM-DD hh:mm:ss is supported.
  • Value range: 1970-01-01 00:00:01' UTC to 2038-01-19 03:14:07' UTC.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If p is set to a value greater than 6, the value 6 is used.

8 bytes

YEAR

Used to store year information.

  • Input format: YYYY (four digits) and YY (two digits at most) are supported. For a two-digit year, 70–99 corresponds to 1970–1999 and 00–69 corresponds to 2000–2069 by default.
  • Output format: Only YYYY is supported.
  • Value range: 1901 to 2155.

4 bytes
  • M compatibility does not support the following ODBC syntax literals:

    { d 'str' }

    { t 'str' }

    { ts 'str' }

  • M compatibility supports the following standard SQL literals:

    DATE'str'

    TIME 'str'

    TIMESTAMP'str'

  • The data of the time type automatically ignores all zeros at the end of the data when it is displayed.
  • In strings with separators, the backslash (\) is regarded as a common punctuation character instead of an escape character in the M-compatible database.
  • The default value of p is 0.
  • If the value of sql_mode contains strict_trans_tables and the input value is invalid or out of the value range, an error is reported. If strict_trans_tables is not contained and the input value is invalid or out of the value range, 0 is stored.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
-- Create a table.
m_db=# CREATE TABLE temporal_date(a DATE);
m_db=# CREATE TABLE temporal_time(b TIME);
m_db=# CREATE TABLE temporal_datetime(c DATETIME(5));
m_db=# CREATE TABLE temporal_timestamp(d TIMESTAMP);
m_db=# CREATE TABLE temporal_year(e YEAR);
-- Insert data.
m_db=# INSERT INTO temporal_date VALUES ('2020-02-02'); 
m_db=# INSERT INTO temporal_date VALUES (date'2020-02-02');
m_db=# INSERT INTO temporal_date VALUES (20200202);
m_db=# INSERT INTO temporal_time VALUES ('20 10:00:00');
m_db=# INSERT INTO temporal_time VALUES ('800:00:00');
m_db=# INSERT INTO temporal_time VALUES (time'200:00:00');
m_db=# INSERT INTO temporal_datetime VALUES ('2020-02-02T04:04:04');
m_db=# INSERT INTO temporal_datetime VALUES (timestamp'2020-02-02 10:00:00');
m_db=# INSERT INTO temporal_datetime VALUES (20201010010101);
m_db=# INSERT INTO temporal_timestamp VALUES ('2020-02-02 10:00:00');
m_db=# INSERT INTO temporal_timestamp VALUES (20200220101010);
m_db=# INSERT INTO temporal_year VALUES (2020);
m_db=# INSERT INTO temporal_year VALUES (20);
-- View data.
m_db=# SELECT * FROM temporal_date;
     a      
------------
 2020-02-02
 2020-02-02
 2020-02-02
(3 rows)

m_db=# SELECT * FROM temporal_time;
     b     
-----------
 490:00:00
 800:00:00
 200:00:00
(3 rows)

m_db=# SELECT * FROM temporal_datetime;
             c             
---------------------------
 2020-02-02 04:04:04.00000
 2020-02-02 10:00:00.00000
 2020-10-10 01:01:01.00000
(3 rows)

m_db=# SELECT * FROM temporal_timestamp;
          d          
---------------------
 2020-02-02 10:00:00
 2020-02-20 10:10:10
(2 rows)

m_db=# SELECT * FROM temporal_year;
  e   
------
 2020
 2020
(2 rows)

-- Drop the table.
m_db=# DROP TABLE temporal_date;
m_db=# DROP TABLE temporal_time;
m_db=# DROP TABLE temporal_datetime;
m_db=# DROP TABLE temporal_timestamp;
m_db=# DROP TABLE temporal_year;
Parent topic: Data Types