更新时间:2026-10-21 GMT+08:00
日期时间类型
M-Compatibility支持的日期时间类型如表1所示。
| 名称 | 描述 | 存储空间 |
|---|---|---|
| DATE | 用于保存年月日信息,即日期信息。
| 4个字节。 |
| TIME[(p)] | 用于保存时分秒和毫秒信息,即时间信息。
| 8个字节。 |
| DATETIME[(p)] | 用于保存年月日时分秒以及毫秒信息,即日期时间信息。
| 8个字节。 |
| TIMESTAMP[(p)] | 用于保存同时包含日期信息和时间信息的数据,受到数据库时区的影响。
| 8个字节。 |
| YEAR | 用于保存年份信息。
| 4个字节。 |
- M-Compatibility支持标准SQL字面量:
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
父主题: 数据类型