更新时间:2026-10-21 GMT+08:00
分享

日期时间类型

M-Compatibility支持的日期时间类型如表1所示。

表1 日期时间类型

名称

描述

存储空间

DATE

用于保存年月日信息,即日期信息。

  • 输入格式:支持有分隔符的字符串'YYYY-MM-DD'和无分隔符的字符串'YYYYMMDD'以及数字YYYYMMDD的输入格式,其中年份可以只输入2位,此时70~99默认为1970~1999,00~69默认为2000~2069。
    • 有分隔符场景下,任意标点符号可作为分隔符。
  • 输出格式:仅支持YYYY-MM-DD。
  • 范围:1000-01-01 ~ 9999-12-31。
    • sql_mode参数值包含“allow_invalid_dates”时,月1-12与日1-31可以自由组合,即2月31日也可以存入。
    • sql_mode参数值不包含“no_zero_in_date”时,允许月和日为0,即1999-00-00为合法值。
    • sql_mode参数值不包含“no_zero_date”时,允许年月日均为0,即0000-00-00为合法值。

4个字节。

TIME[(p)]

用于保存时分秒和毫秒信息,即时间信息。

  • 输入格式:支持有分隔符的字符串'hh:mi:ss'和无分隔符的字符串'hhmiss'以及数字hhmiss的输入格式,支持输入日即天数信息的字符串'D hh:mi:ss',支持毫秒输入,最终会四舍五入截断至指定精度。
    • 有分隔符场景下,仅单个英文冒号可作为分隔符。
  • 输出格式:支持hh:mi:ss。
  • 范围:-838:59:59.000000 ~ 838:59:59.000000。
  • 精度:p表示小数点后的精度,取值范围为0~6。当p设置超过6时,按照p为6进行处理。

8个字节。

DATETIME[(p)]

用于保存年月日时分秒以及毫秒信息,即日期时间信息。

  • 输入格式:支持有分隔符的字符串'YYYY-MM-DD'和无分隔符的字符串'YYYYMMDD'以及数字YYYYMMDD的输入格式,其中年份可以只输入2位,此时70~99默认为1970~1999,00~69默认为2000~2069,支持毫秒输入,最终会四舍五入截断至指定精度。
    • 有分隔符场景下,在日期部分或时间部分中任意标点符号可作为分隔符,毫秒前的分隔符只能是小数点。
    • 年月日与时分秒之间允许若干空格或单个大写字母T作为间隔,其中空格仅应用于有分隔符的字符串。
  • 输出格式:仅支持YYYY-MM-DD hh:mi:ss。
  • 范围:1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。
    • sql_mode参数值包含“allow_invalid_dates”时,月1-12与日1-31可以自由组合,即2月31日也可以存入。
    • sql_mode参数值不包含“no_zero_in_date”时,允许月和日为0,即1999-00-00 10:00:00为合法值。
    • sql_mode参数值不包含“no_zero_date”时,允许年月日均为0,即0000-00-00 10:00:00为合法值。
  • 精度:p表示小数点后的精度,取值范围为0~6。当p设置超过6时,按照p为6进行处理。

8个字节。

TIMESTAMP[(p)]

用于保存同时包含日期信息和时间信息的数据,受到数据库时区的影响。

  • 输入格式:支持有分隔符的字符串'YYYY-MM-DD hh:mi:ss'和无分隔符的字符串'YYYYMMDDhhmiss'以及数字YYYYMMDDhhmiss的输入格式,其中年份可以只输入2位时,此时70~99默认为1970~1999,00~69默认为2000~2069,支持毫秒输入,最终会四舍五入截断至指定精度。
    • 有分隔符场景下,在日期部分或时间部分中任意标点符号可作为分隔符,毫秒前的分隔符只能是小数点。
    • 年月日与时分秒之间允许若干空格或单个大写字母T作为间隔,其中空格仅应用于有分隔符的字符串。
  • 输出格式:仅支持YYYY-MM-DD hh:mi:ss。
  • 范围:1970-01-01 00:00:01' UTC ~ 2038-01-19 03:14:07' UTC。
  • 精度:p表示小数点后的精度,取值范围为0~6。当p设置超过6时,按照p为6进行处理。

8个字节。

YEAR

用于保存年份信息。

  • 输入格式:支持4位的YYYY和至多两位的YY,此时70~99默认为1970~1999,00~69默认为2000~2069。
  • 输出格式:仅支持YYYY。
  • 范围:1901 ~ 2155。

4个字节。

  • M-Compatibility支持标准SQL字面量:

    DATE 'str'

    TIME 'str'

    TIMESTAMP 'str'

  • 时间类型的数据在显示的时候会自动忽略末尾的所有零。
  • 有分隔符的字符串输入中反斜杠“\”在M-Compatibility中视作普通标点符号而非转义字符标志。
  • 精度p默认取值为0。
  • sql_mode参数值包含“strict_trans_tables”时,非法输入或者超过范围会报错,不包含时非法输入或者超过范围存入0值。
  • 向TIME类型中插入可被解析为日期/时间类型的VARCHAR类型数据(超过11位的数字,如:100001101011)时,如果VARCHAR类型中表示月份的部分为00时的行为:
    • 设置兼容性参数m_format_dev_version为s2以上时,将VARCHAR类型数据中,表示日期的部分的数据乘以24,并将结果添加到VARCHAR类型数据中表示小时部分的数据上。
    • 设置兼容性参数m_format_dev_version为空、s1或s2时,将VARCHAR类型数据中,表示时间的部分(时:分:秒)原样输出。
  • 向TIMESTAMP(n)(n ≤ 5)中插入具有TIMESTAMP(6)精度且超出n指定精度的数据时:
    • 设置兼容性参数m_format_dev_version为空、s1或s2时,无论GUC参数sql_mode是否包含strict_trans_tables或strict_all_tables,均不会报错,且不会生成告警信息。
    • 设置兼容性参数m_format_dev_version为s2以上时,当GUC参数sql_mode值中包含strict_trans_tables或strict_all_tables时,插入报错;当GUC参数sql_mode值中不包含strict_trans_tables以及strict_all_tables时,生成告警信息。
  • 当DATE类型的非法值转换为JSON类型时:
    • 设置兼容性参数m_format_dev_version为空、s1或s2时,均不会报错,且不会生成告警信息。
    • 设置兼容性参数m_format_dev_version为s2以上时,会生成告警信息,且转换后的JSON值为"0000-00-00"。

示例:

  • 示例一:
     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
    --创建表
    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);
    --插入数据
    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);
    --查看数据
    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)
    
    --删除表
    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;
    
  • 示例二:m_format_dev_version为s3时的行为变更
    -- 控制向TIME中插入类似“100001101011”的VARCHAR类型的数据,且表示月份的部分为00时的行为:
    m_db=# SET m_format_dev_version='s2';
    SET
    m_db=# DROP TABLE IF EXISTS t_time;
    DROP TABLE
    m_db=# CREATE TABLE t_time(a time);
    CREATE TABLE
    
    m_db=# INSERT INTO t_time VALUES('100001101011');
    INSERT 0 1
    
    m_db=# SELECT * FROM t_time;
        a     
    ----------
     10:10:11
    (1 row)
    
    -- 设置兼容性参数为s3。
    m_db=# SET m_format_dev_version='s3';
    SET
    m_db=# TRUNCATE t_time;
    
    m_db=# INSERT INTO t_time VALUES('100001101011');
    INSERT 0 1
    
    m_db=# SELECT * FROM t_time;
        a     
    ----------
     34:10:11
    (1 row)
    
    m_db=# DROP TABLE IF EXISTS t_time ;
    DROP TABLE
    
    -- 控制向 TIMESTAMP(n)(n ≤ 5)中插入具有 TIMESTAMP(6) 精度且超出n指定精度的数据时的行为。
    m_db=# SET m_format_dev_version=s2;
    m_db=# SET time_zone='+0:00';
    m_db=# SET sql_mode = traditional;
    m_db=# DROP TABLE IF EXISTS t_timestamp, t_timestamp6;
    m_db=# CREATE TABLE t_timestamp(a timestamp);
    m_db=# CREATE TABLE t_timestamp6(a timestamp(6));
    m_db=# INSERT INTO t_timestamp6 VALUES('2038-01-19 03:14:07.6');
    
    -- 严格模式下插入。
    m_db=# SET sql_mode = traditional;
    SET
    m_db=# INSERT INTO t_timestamp SELECT a FROM t_timestamp6;
    
    m_db=# -- 宽松模式下插入。
    m_db=# SET sql_mode = '';
    m_db=# INSERT INTO t_timestamp SELECT a FROM t_timestamp6;
    
    m_db=# SELECT * FROM t_timestamp;
              a          
    ---------------------
     0000-00-00 00:00:00
     0000-00-00 00:00:00
    (2 rows)
    
    -- 设置兼容性参数为s3。
    m_db=# SET m_format_dev_version=s3;
    m_db=# TRUNCATE t_timestamp;
    
    -- 严格模式下插入。
    m_db=# SET sql_mode = traditional;
    m_db=# INSERT INTO t_timestamp SELECT a FROM t_timestamp6;
    ERROR: The date, time, datetime, timestamp, or year is incorrect.
    CONTEXT: referenced column: a
    
    -- 宽松模式下插入。
    m_db=# SET sql_mode = '';
    m_db=# INSERT INTO t_timestamp SELECT a FROM t_timestamp6;
    WARNING:  Out of range for column.
    CONTEXT:  referenced column: a
    INSERT 0 1
    m_db=# SELECT * FROM t_timestamp;
              a          
    ---------------------
     0000-00-00 00:00:00
    (1 row)
    m_db=# DROP TABLE IF EXISTS t_timestamp;
    DROP TABLE
    
    -- 控制NUMERIC类型插入表时,隐式转换为DATETIME、TIMESTAMP、DATE类型时的非法数据检验。
    m_db=# SET m_format_dev_version=s2;
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 date);
    CREATE TABLE
    m_db=# INSERT INTO t1 VALUES(20230101.4);
    INSERT 0 1
    m_db=# SELECT * FROM t1;
         c1     
    ------------
     2023-01-01
    (1 row)
    
    m_db=# SET m_format_dev_version=s3;
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 date);
    CREATE TABLE
    m_db=# INSERT INTO t1 VALUES(20230101.4);
    ERROR:  The date, time, datetime, timestamp, or year is incorrect.
    CONTEXT:  referenced column: c1
    m_db=# SELECT * FROM t1;
     c1 
    ----
    (0 rows)
    
    -- 控制DATETIME、TIMESTAMP类型插入表时,隐式转换为DATE、TIME、INT等类型时的行为。
    m_db=# SET m_format_dev_version=s2;
    SET
    m_db=# DROP TABLE IF EXISTS t1,t2;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 time(3));
    CREATE TABLE
    m_db=# CREATE TABLE t2(c1 datetime(6));
    CREATE TABLE
    -- 当插入数据的小数位大于time(3)的精度时,数据截断。
    m_db=# INSERT INTO t2 VALUES('2023-01-01 10:11:12.9995');
    INSERT 0 1
    m_db=# INSERT INTO t1 SELECT c1 FROM t2;
    INSERT 0 1
    m_db=# SELECT * FROM t1;
          c1      
    --------------
     10:11:12.999
    (1 row)
    
    m_db=# SET m_format_dev_version=s3;
    SET
    m_db=# DROP TABLE IF EXISTS t1,t2;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 time(3));
    CREATE TABLE
    m_db=# CREATE TABLE t2(c1 datetime(6));
    CREATE TABLE
    -- 当插入数据的小数位大于time(3)的精度时,四舍五入。
    m_db=# INSERT INTO t2 VALUES('2023-01-01 10:11:12.9995');
    INSERT 0 1
    m_db=# INSERT INTO t1 SELECT c1 FROM t2;
    INSERT 0 1
    m_db=# SELECT * FROM t1;
          c1      
    --------------
     10:11:13.000
    (1 row)
    
    -- 控制字符串类型插入表时,隐式转换为DATE类型时的行为。
    m_db=# SET m_format_dev_version=s2;
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 date);
    CREATE TABLE
    m_db=# INSERT INTO t1 VALUES('2023-12-31 23:59:59.5');
    INSERT 0 1
    m_db=# SELECT * FROM t1;
         c1     
    ------------
     2023-12-31
    (1 row)
    
    m_db=# SET m_format_dev_version=s3;
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(c1 date);
    CREATE TABLE
    -- 会将'2023-12-31 23:59:59.5'进位为'2024-01-01 00:00:00'后转DATE类型。
    m_db=# INSERT INTO t1 VALUES('2023-12-31 23:59:59.5');
    INSERT 0 1
    m_db=# SELECT * FROM t1;
         c1     
    ------------
     2024-01-01
    (1 row)
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE

相关文档