文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ MySQL语法迁移/ 数据类型/ 日期和时间类型
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

日期和时间类型

概述

本节介绍如下日期和时间类型：DATETIME、TIME、TIMESTAMP、YEAR。GaussDB(DWS)不支持以上类型，DSC工具将会对其转换。

类型对照

表1 日期和时间类型对照表

MySQL日期时间类型

MySQL INPUT

GaussDB(DWS) OUTPUT

DATETIME

DATETIME[(fsp)]

TIMESTAMP[(fsp)] WITHOUT TIME ZONE

TIME

TIME[(fsp)]

TIME[(fsp)] WITHOUT TIME ZONE

TIMESTAMP

TIMESTAMP[(fsp)]

TIMESTAMP[(fsp)] WITH TIME ZONE

YEAR

YEAR[(4)]

SMALLINT(4)

该fsp值如果给出，则必须在0到6的范围内。值为0表示没有小数部分。如果省略，则默认精度为0。

输入示例DATETIME

1
2
3
4
5
6
7
 
CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
    `dataType_1` DATETIME,
    `dataType_2` DATETIME(0),
    `dataType_3` DATETIME(6),
    `dataType_4` DATETIME DEFAULT NULL,
    `dataType_5` DATETIME DEFAULT '2018-10-12 15:27:33.999999'
);

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "datatype_1" TIMESTAMP WITHOUT TIME ZONE,
  "datatype_2" TIMESTAMP(0) WITHOUT TIME ZONE,
  "datatype_3" TIMESTAMP(6) WITHOUT TIME ZONE,
  "datatype_4" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  "datatype_5" TIMESTAMP WITHOUT TIME ZONE DEFAULT '2018-10-12 15:27:33.999999'
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_1");

输入示例TIME

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
    `dataType_1` TIME DEFAULT '20:58:10',
    `dataType_2` TIME(3) DEFAULT '20:58:10',
    `dataType_3` TIME(6) DEFAULT '20:58:10',
    `dataType_4` TIME(6) DEFAULT '2018-10-11 20:00:00',
    `dataType_5` TIME(6) DEFAULT '20:58:10.01234',
    `dataType_6` TIME(6) DEFAULT '2018-10-11 20:00:00.01234',
    `dataType_7` TIME DEFAULT NULL,
    `dataType_8` TIME(6) DEFAULT NULL,
    PRIMARY KEY (dataType_1)
);

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "datatype_1" TIME WITHOUT TIME ZONE DEFAULT '20:58:10',
  "datatype_2" TIME(3) WITHOUT TIME ZONE DEFAULT '20:58:10',
  "datatype_3" TIME(6) WITHOUT TIME ZONE DEFAULT '20:58:10',
  "datatype_4" TIME(6) WITHOUT TIME ZONE DEFAULT '2018-10-11 20:00:00',
  "datatype_5" TIME(6) WITHOUT TIME ZONE DEFAULT '20:58:10.01234',
  "datatype_6" TIME(6) WITHOUT TIME ZONE DEFAULT '2018-10-11 20:00:00.01234',
  "datatype_7" TIME WITHOUT TIME ZONE DEFAULT NULL,
  "datatype_8" TIME(6) WITHOUT TIME ZONE DEFAULT NULL,
  PRIMARY KEY ("datatype_1")
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_1");

输入示例TIMESTAMP

1
2
3
4
5
6
7
8
9
 
CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
   `dataType_1` TIMESTAMP,
   `dateType_4` TIMESTAMP DEFAULT '2018-10-12 15:27:33',
   `dateType_5` TIMESTAMP DEFAULT '2018-10-12 15:27:33.999999',
   `dateType_6` TIMESTAMP DEFAULT '2018-10-12 15:27:33',
   `dateType_7` TIMESTAMP DEFAULT '2018-10-12 15:27:33',
   `dataType_8` TIMESTAMP(0) DEFAULT '2018-10-12 15:27:33',
   `dateType_9` TIMESTAMP(6) DEFAULT '2018-10-12 15:27:33'
);

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "datatype_1" TIMESTAMP WITH TIME ZONE,
  "datetype_4" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33',
  "datetype_5" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33.999999',
  "datetype_6" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33',
  "datetype_7" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33',
  "datatype_8" TIMESTAMP(0) WITH TIME ZONE DEFAULT '2018-10-12 15:27:33',
  "datetype_9" TIMESTAMP(6) WITH TIME ZONE DEFAULT '2018-10-12 15:27:33'
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_1");

输入示例YEAR

1
2
3
4
5
6
 
CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
    `dataType_1` YEAR,
    `dataType_2` YEAR(4),
    `dataType_3` YEAR DEFAULT '2018',
    `dataType_4` TIME DEFAULT NULL
);

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "datatype_1" SMALLINT,
  "datatype_2" SMALLINT,
  "datatype_3" VARCHAR(4) DEFAULT '2018',
  "datatype_4" TIME WITHOUT TIME ZONE DEFAULT NULL 
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_1");
父主题： 数据类型