虚谷文档 v1.0.0

适用虚谷数据库版本

v12.10.13



适用虚谷数据库版本

v12.10.13


时间数据类型 ​

📄字数 6.8K
👁️阅读量 加载中...

XuguDB支持所有标准SQL时间类型，包括日期、时间、日期时间、时间戳、时间间隔。

时间类型相关分类如下表所示:

含义类型
日期DATE
时间TIME、TIME WITH TIME ZONE
日期类型DATETIME、DATETIME WITH TIME ZONE
时间戳TIMESTAMP、TIMESTAMP WITH TIME ZONE
时间间隔INTERVAL YEAR、INTERVAL MONTH、INTERVAL DAY、INTERVAL HOUR、INTERVAL MINUTE、INTERVAL SECOND、
INTERVAL YEAR TO MONTH、INTERVAL DAY TO HOUR、INTERVAL DAY TO MINUTE、INTERVAL DAY TO SECOND、
INTERVAL HOUR TO MINUTE、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND

日期类型 ​

DATE类型是数据库中专门存储日历日期的数据类型，精确表示年、月、日，不包含时间成分，支持日期计算、比较和格式化操作。

  • 语法格式
sql
DATE

下表展示了DATE类型的字节格式、长度和表示范围：

数据类型格式字节长度范围
DATEYYYY-MM-DD4字节公元前：'9999-12-31 BC'到'0002-01-01 BC'
公元后：'0001-01-01'到'9999-12-31'

提示

  • 使用字符串或TO_DATE函数赋值，公元前、公元后日期分别以BC和AD表示，位于时间字符串末尾。
  • Oracle兼容模式时，DATE类型映射为DATETIME类型。

示例：

DATE类型，创建DATE类型字段表，插入数据和查询数据

sql
SQL> CREATE TABLE tab_date_test(col1 DATE);
 
SQL> INSERT INTO tab_date_test VALUES('2025-6-20');
  
SQL> SELECT * FROM tab_date_test;
+-------------+
|    COL1     |
+-------------+
| 2025-06-20  |
+-------------+

时间类型 ​

TIME类型用于存储一天内的具体时刻，精确表示时、分、秒，不包含日期和时区信息；而TIME WITH TIME ZONE在时刻基础上附加时区偏移量，明确标识该时刻对应的时区位置，用于跨时区时间协调。

  • 语法格式
sql
TIME[size]
TIME WITH TIME ZONE
  • 可选size值用来指定小数位秒的精度，范围为[0,3]。0值表示小数部分截断, 如果省略，则默认精度为0。

提示

  • XuguDB控制台输出TIME类型可显示精度部分
  • 如果以JDBC输出TIME类型无精度部分

下表展示了时间类型的字节格式、长度和表示范围：

数据类型格式字节长度范围
TIMEHH24:MI:SS4字节'00:00:00.000'到'23:59:59.999
TIME WITH TIME ZONEHH24:MI:SS +UTC6字节'00:00:00.000'到'23:59:59.999
UTC:-12:59到+14:59

示例：
时间类型，创建TIME类型与TIME WITH TIME ZONE字段表，插入数据和查询数据

sql
SQL> CREATE TABLE tab_time_test(col1 TIME, col2 TIME WITH TIME ZONE);
 
SQL> INSERT INTO tab_time_test VALUES('15:16:25','17:30:29+08:00');
  
SQL> SELECT * FROM tab_time_test;
+--------------+-----------------+
|     COL1     |      COL2       |
+--------------+-----------------+
| 15:16:25.000 | 17:30:29 +08:00 |
+--------------+-----------------+

日期时间类型 ​

DATETIME类型存储日期和时间，不包含时区信息；DATETIME WITH TIME ZONE在日期时间基础上附加时区偏移，明确记录特定时区的绝对时间，支持自动跨时区转换。

  • 语法格式
sql
DATETIME
DATETIME WITH TIME ZONE

提示

  • 使用字符串或TO_DATE函数赋值公元前、公元后日期分别以BC和AD表示，位于时间字符串末尾。

下表展示了日期时间类型的字节格式、长度和表示范围：

数据类型格式字节长度范围
DATETIMEYYYY-MM-DD HH24:MI:SS8字节公元前：'9999-12-31 23:59:59.999 BC'到'0002-01-01 00:00:00.000 BC'
公元后：'0001-01-01 00:00:00.000'到'9999-12-31 23:59:59.999'
DATETIME WITH TIME ZONEYYYY-MM-DD HH24:MI:SS +UTC10字节公元前：'9999-12-31 23:59:59.999 BC'到'0002-01-01 00:00:00.000 BC'
公元后：'0001-01-01 00:00:00.000'到'9999-12-31 23:59:59.999'
UTC:-12:59到+14:59

示例：

日期时间类型，创建DATETIME类型与DATETIME WITH TIME ZONE字段表，插入数据和查询数据。

sql
SQL> CREATE TABLE tab_datetime_test(col1 DATETIME, col2 DATETIME WITH TIME ZONE);
 
SQL> INSERT INTO tab_datetime_test VALUES('2025-06-20 15:16:25','2025-06-21 17:16:25+08:00');
  
SQL> SELECT * FROM tab_datetime_test;
+--------------------------+---------------------------------+
|           COL1           |              COL2               |
+--------------------------+---------------------------------+
| 2025-06-20 15:16:25.000  | 2025-06-21 17:16:25.000  +08:00 |
+--------------------------+---------------------------------+

时间戳 ​

时间戳类型存储年、月、日、时、分、秒、毫秒、时区（可选），范围与日期时间类型一致，时间戳类型默认插入当前系统时间，可根据需求设置记录变更时自动变更该行数据时间戳。

  • 语法格式
sql
TIMESTAMP[size] [AUTO UPDATE]
TIMESTAMP[size] WITH TIME ZONE [AUTO UPDATE]
  • 可选size值用来指定小数位秒的精度，范围为[0,6]。实际精度最大显示三位，0表示小数部分截断，如果省略，则默认精度为3。
  • 可选AUTO UPDATE可用于执行UPDATE时自动变更该行数据时间戳。不加AUTO UPDATE，默认INSERT时自动变更。

下表展示了时间戳类型的字节格式、长度和表示范围：

数据类型格式字节长度范围
TIMESTAMPYYYY-MM-DD HH24:MI:SS8字节公元前：'9999-12-31 23:59:59.999 BC'到'0002-01-01 00:00:00.000 BC'
公元后：'0001-01-01 00:00:00.000'到'9999-12-31 23:59:59.999'
TIMESTAMP WITH TIME ZONEYYYY-MM-DD HH24:MI:SS10字节公元前：'9999-12-31 23:59:59.999 BC'到'0002-01-01 00:00:00.000 BC'
公元后：'0001-01-01 00:00:00.000'到'9999-12-31 23:59:59.999'

提示

  • 表字段类型为时间戳类型时，当不插入数据时，数据库会默认插入当前的时间，而对于日期时间类型不会插入当前时间。

示例：

时间戳类型，创建TIMESTAMP类型与TIMESTAMP WITH TIME ZONE字段表，插入数据和查询数据。

sql
SQL> CREATE TABLE tab_timestamp_test(col1 TIMESTAMP, col2 TIMESTAMP WITH TIME ZONE);
 
SQL> INSERT INTO tab_timestamp_test VALUES('2025-06-20 15:16:25','2025-06-21 17:16:25+08:00');
  
SQL> SELECT * FROM tab_timestamp_test;
+--------------------------+---------------------------------+
|           COL1           |              COL2               |
+--------------------------+---------------------------------+
| 2025-06-20 15:16:25.000  | 2025-06-21 17:16:25.000  +08:00 |
+--------------------------+---------------------------------+

时间间隔类型 ​

时间间隔类型是数据库中专门表示时间长度或时间跨度的数据类型，用于对日期/时间值进行精确的加减运算、计算时间差以及定义时间窗口。

  • INTERVAL 类型主要包括两大类（年月间隔、日-时间隔），具体概念描述如下表：
类型说明
年月间隔
INTERVAL YEAR年维度
INTERVAL MONTH月维度
INTERVAL YEAR TO MONTH年-月复合
天间隔
INTERVAL DAY日维度
INTERVAL HOUR小时维度
INTERVAL MINUTE分钟维度
INTERVAL SECOND仅秒（含微秒）维度
INTERVAL DAY TO HOUR日-小时复合
INTERVAL DAY TO MINUTE日-分钟复合
INTERVAL DAY TO SECOND日-秒复合
INTERVAL HOUR TO MINUTE小时-分钟复合
INTERVAL HOUR TO SECOND小时-秒复合
INTERVAL MINUTE TO SECOND表示分钟-秒复合
  • 语法格式
sql
INTERVAL YEAR[size]
INTERVAL MONTH[size]
INTERVAL DAY[size]
INTERVAL HOUR[size]
INTERVAL MINUTE[size]
INTERVAL SECOND[size, size]
INTERVAL YEAR[size] TO MONTH
INTERVAL DAY[size] TO HOUR
INTERVAL DAY[size] TO MINUTE
INTERVAL DAY[size] TO SECOND[size]
INTERVAL HOUR[size] TO MINUTE
INTERVAL HOUR[size] TO SECOND[size]
INTERVAL MINUTE[size] TO SECOND[size]

下表展示了时间间隔类型的字节长度、表示范围、SIZE：

数据类型字节长度范围SIZE
INTERVAL YEAR4[-999999999, 999999999][0, 9]
INTERVAL MONTH4[-2147483648, 2147483647][0, 10]
INTERVAL YEAR TO MONTH4year：[-178956970, 178956970]
month：[0, 11]		[0, 9]
INTERVAL DAY4[-2147483648, 2147483647][0, 10]
INTERVAL HOUR4[-999999999, 999999999][0, 9]
INTERVAL MINUTE4[-999999999, 999999999][0, 9]
INTERVAL SECOND8[-9223372036854.775808, 9223372036854.775807]1：[0, 13]
2：[0, 6]
INTERVAL DAY TO HOUR4day：[-89478485, 89478485]
hour：[-2147483648, 2147483647]		[0, 8]
INTERVAL DAY TO MINUTE4day：[-1491308, 1491308]
hour：[-35791394, 35791394]
minute[0, 59]		[0, 7]
INTERVAL DAY TO SECOND8day：[-106751991, 106751991]
hour：[-2562047788, 2562047788]
minute[0, 59]
second：[0, 60]		1：[0, 9]
2：[0, 6]
INTERVAL HOUR TO MINUTE4hour：[-9999999, 9999999]
minute：[0, 59]		[0, 7]
INTERVAL HOUR TO SECOND8hour：[-2562047788, 2562047788]
minute：[0, 59]
second：[0, 60]		1：[0, 10]
2：[0, 6]
INTERVAL MINUTE TO SECOND8minute：[-999999999, 999999999]
second：[0, 60]		1：[0, 9]
2：[0, 6]

提示

  • 对于时间间隔类型不指定 SIZE 时，默认值取 SIZE 范围最大值。
  • 类型单元必须包含 SECOND，小数部分精度满足四舍五入。
  • 表中对于多单元组成的类型范围，并不表示整体类型的范围，而是单一单元最大范围，是其他单元为0时计算所得。

输出格式 ​

XuguDB 支持通过 DEF_INTERVAL_STYLE 系统参数切换输出格式，默认值为SQL_STANDARD，支持以下4种格式：SQL_STANDARD（0）、ISO_8601（1）、POSTGRES（2）、POSTGRES_VERBOSE（3），输出时会按对应格式还原换算后的单位数值。

SQL标准格式 ​

sql_standard：
在 SQL 标准中，13种 INTERVAL 类型是通过起始字段TO终止字段的语法精确定义的，被严格的划分为'年-月'和'日-时间'两大类别，输出格式由定义时的数据类型决定，结构固定。SQL标准输出格式如下表所示：

类别类型说明与示例
年-月间隔（只包含YEAR/MONTH）INTERVAL YEAR
INTERVAL MONTH
INTERVAL YEAR TO MONTH		仅处理年月。
例如：INTERVAL '2' YEAR，表示2年，输出格式：2-0
INTERVAL '5' MONTH 表示5个月，输出格式：0-5
INTERVAL '1-6' YEAR TO MONTH表示1年6个月，输出格式：1-6
日-时间间隔（只包含DAY/HOUR/MINUTE/SECOND）INTERVAL DAY
INTERVAL HOUR
INTERVAL MINUTE
INTERVAL SECOND
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL HOUR TO MINUYE
INTERVAL HOUR TO SECOND
INTERVAL MINUTE TO SECOND		仅处理日以下时间单位。
例如：INTERVAL '3' DAY 表示3天，输出格式:3 0:00:00
INTERVAL '3' HOUR 表示3小时，输出格式：3:00:00
INTERVAL '3' MINUTE 表示3分钟，输出格式：0:03:00
INTERVAL '3 12:48:56' DAY TO SECOND 表示3天12小时48分56秒，输出格式：3 12:48:56。一个值中可包含从起始到终止字段的所有中间单位，其他类型类似。

iso_8601：
ISO 8601 标准提供了一种通用，灵活的表达语法（即：PnYnMnDTnHnMnS 格式），可用这个格式描述任意时间单位的组合：ISO 8601 与 SQL 标准对照表如下:

SQL标准间隔对应ISO 8601格式示例灵活性体现
年-月类型T之前的部分（PnYnMn）
INTERVAL YEARP1Y使用Y（年）组件
INTERVAL MONTHP6M使用M（月）组件
INTERVAL YEAR TO MONTHP1Y6M组合Y和M组件
日-时间类型T之后的部分
INTERVAL DAYP3D使用D（日）组件
INTERVAL HOURPT12H使用H（时）组件，注意：即使没有日期部分，字母T也必须保留
INTERVAL MINUTEPT30M使用M（分）组件
INTERVAL SECONDPT45.55S使用S（组件），注意：秒可包含小数
INTERVAL DAY TO HOURP3DT12H组合D和H组件
INTERVAL DAY TO MINUTEP4DT14H56M组合D、H、M组件
INTERVAL DAY TO SECONDP6DT7H12M23.23S包含所有组件
INTERVAL HOUR TO MINUTEPT12H23M组合H和M组件
INTERVAL HOUR TO SECONDPT26H56M56.23S组合H、M、S组件
INTERVAL MINUTE TO SECONDPT30M15S组合M、S组件

核心规则：

  • 标记化结构：每个数值后面必须紧跟一个单一字母（Y、M、D、H、M、S）的设计来指明单位，完全消除歧义。
  • 日期与时间的分隔：字母 T 是日期部分（PnYnMnD）与时间部分（TnHnMnS）的强制分隔符，所以即使没有日期部分，T 也必须出现在时间部分之前（如：PT1H）。

兼容格式 ​

postgres、postgres_verbose：
PostgreSQL 的 INTERVAL 类型有两种传统输出格式，postgres、postgres_verbose 其设计初衷是为了兼容之前的版本。下表展示了核心区别：

特性postgrespostgres_verbose
语法特点混合使用英文单位缩写与时间表示使用完整的英文时间单位
示例31 days 04:05:06@ 31 days 4 hours 5 mins 6 secs

示例：

时间间隔类型，创建间隔类型字段表，插入数据和查询数据。

sql
SQL> CREATE TABLE tab_interval_test(col1 INTERVAL YEAR,
                                    col2 INTERVAL MONTH,
                                    col3 INTERVAL DAY,
                                    col4 INTERVAL HOUR,
                                    col5 INTERVAL MINUTE,
                                    col6 INTERVAL SECOND,
                                    col7 INTERVAL YEAR TO MONTH,
                                    col8 INTERVAL DAY TO HOUR,
                                    col9 INTERVAL DAY TO MINUTE,
                                    col10 INTERVAL DAY TO SECOND,
                                    col11 INTERVAL HOUR TO MINUTE,
                                    coll2 INTERVAL HOUR TO SECOND,
                                    col13 INTERVAL MINUTE TO SECOND);
 
SQL> INSERT INTO tab_interval_test VALUES(2025, 1000, 1996, 5200, 12345, 1999.9898, '2025-11', '66666 23', '9999 23:56', '2222 12:56:24.233', '888:26', '9999999:59:59.999999', '9999999:59.999999');
  
SQL> SELECT * FROM tab_interval_test;

+--------+------+--------------+------------+-----------+--------------+---------+----------------+---------------+-------------------+-----------+----------------------+---------------------+
|  COL1  | COL2 |     COL3     |    COL4    |   COL5    |     COL6     |  COL7   |      COL8      |     COL9      |       COL10       |   COL11   |        COLL2         |        COL13        |
+--------+------+--------------+------------+-----------+--------------+---------+----------------+---------------+-------------------+-----------+----------------------+---------------------+
| 2025-0 | 83-4 | 1996 0:00:00 | 5200:00:00 | 205:45:00 | 0:33:19.9898 | 2025-11 | 66666 23:00:00 | 9999 23:56:00 | 2222 12:56:24.233 | 888:26:00 | 9999999:59:59.999999 | 166666:39:59.999999 |
+--------+------+--------------+------------+-----------+--------------+---------+----------------+---------------+-------------------+-----------+----------------------+---------------------+