优化查询性能：DolphinDB 时间类型数据比较规则详解

在数据库中，时间是一种常见的数据类型。在处理时间数据时，比较操作是非常常见的需求。然而，在不同的场景下，对时间类型数据进行比较时应用的规则不同。本文将从 DolphinDB 支持的时间类型开始，由浅入深分别介绍时间类型数据在不同场景下的比较规则。涵盖以下场景：

• 时间类型的转换规则
• 时间类型的比较规则
• 时间类型的分区剪枝规则

1. 时间类型介绍

DolphinDB 支持的时间类型包括：TIME, MINUTE, SECOND, DATE, MONTH, DATEHOUR, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP。以上数据类型可以按照包含的时间信息分成：

• 日期型：仅包含日期信息，包括 DATE, MONTH
• 时间型：仅包含时间信息，包括 MINUTE, SECOND, TIME, NANOTIME
• 日期时间型：同时包含日期和时间信息，包括 DATEHOUR, DATETIME, TIMESTAMP, NANOTIMESTAMP

以下是每个时间类型格式说明和例子：

DolphinDB 的时间类型不包含时区信息，由用户来决定时间对下的时区。通过 localtime、 gmtime、convertT 函数可以转换时区信息，通过today和now函数可以获取当前的系统时间。

获取当前的日期，可以使用today函数，函数会返回一个 DATE 类型的数据，表示当前的日期：

today()  // 2024.06.03

获取当前的时间，可以使用now函数，默认情况下，该函数返回的是 TIMESTAMP 类型，精确到毫秒；也可以指定参数nanoSecond=true，返回 NANOTIMESTAMP 类型，精确到纳秒。

now()  // 2024.06.03T09:29:38.390
now(true)  // 2024.06.03T09:31:17.318298137

2. 显式的时间类型转换

在 DolphinDB 中，可以使用数据类型转换函数或者cast函数进行数据类型转换。时间类型的转换规则可以总结成下表（横轴为目标数据类型，纵轴为源数据类型，√表示支持转换，x表示不支持转换。）。

	日期型	日期时间型	时间型
日期型	√	√	×
日期时间型	√	√	√
时间型	×	×	√

时间类型的转换规则可概括为：

（1）相同分类中的时间类型可以相互转换。

同为日期型的 DATE 和 MONTH 可以互相转换；

date(2012.01M)   // 2012.01.01
month(2012.01.02)  // 2012.01M

同为时间型的 MINUTE、SECOND、TIME、NANOTIME 可以互相转换；

minute(23:30:00)  // 23:30m
minute(23:30:00.000)  // 23:30m
minute(23:30:00.000000000)  // 23:30m
second(23:30m)  // 23:30:00
second(23:30:00.001)  // 23:30:00
second(23:30:00.000000001)  // 23:30:00
time(23:31m)  // 23:31:00.000
time(23:30:01)  // 23:30:01.000
time(23:30:01.000000001)  // 23:30:01.000
nanotime(23:30m)  // 23:30:00.000000000
nanotime(23:30:31)  // 23:30:31.000000000
nanotime(23:30:31.001)  //23:30:31.001000000

同为日期时间型的 DATEHOUR、DATETIME、TIMESTAMP、NANOTIMESTAMP 可以互相转换。

datehour(2020.01.01 13:30:01)  // 2020.01.01T13
datehour(2020.01.01T13:30:01.001)  //  2020.01.01T13
datehour(2020.01.01T13:30:01.001002003)  // 2020.01.01T13
datetime(datehour(2020.01.01 13:00:01))  // 2020.01.01T13:00:00
datetime(2020.01.01T13:30:01.001)  // 2020.01.01T13:30:01
datetime(2020.01.01T13:30:01.001002003)  //  2020.01.01T13:30:01
timestamp(datehour(2020.01.01 13:00:01))  //  2020.01.01T13:00:00.000
timestamp(2020.01.01 13:00:01)  // 2020.01.01T13:00:01.000
timestamp(2020.01.01T13:30:01.001002003)  // 2020.01.01T13:30:01.001
nanotimestamp(datehour(2020.01.01 13:00:01))  //  2020.01.01T13:00:00.000000000
nanotimestamp(2020.01.01T13:30:01)  //  2020.01.01T13:30:01.000000000
nanotimestamp(2020.01.01T13:30:01.001)  // 2020.01.01T13:30:01.001000000

（2）日期型和日期时间型可以相互转换。

日期型转换为日期时间型，会自动补充时间为0点的信息。

datehour(2023.01.02)  // 2023.01.02T00
datetime(2023.01.02)  // 2023.01.02T00:00:00
timestamp(2023.01.02)  // 2023.01.02T00:00:00.000
nanotimestamp(2023.01.02)  // 2023.01.02T00:00:00.000000000
datehour(2023.01M)  // 2023.01.01T00
datetime(2023.01M)  // 2023.01.01T00:00:00
timestamp(2023.01M)  //  2023.01.01T00:00:00.000
nanotimestamp(2023.01M)  // 2023.01.01T00:00:00.000000000

日期时间型转换为日期型，会舍弃时间信息。

date(datehour(2020.01.01 13:00:01)) // 2020.01.01
date(2020.01.01 13:00:01)  // 2020.01.01
date(2020.01.01 13:00:01.001)  // 2020.01.01
date(2020.01.01 13:00:01.001002003)  // 2020.01.01
month(datehour(2020.01.01 13:00:01))  // 2020.01M
month(2020.01.01 13:00:01)  // 2020.01M
month(2020.01.01 13:00:01.001)  // 2020.01M
month(2020.01.01 13:00:01.001002003)  // 2020.01M

（3）日期时间型可以转换为时间型，但时间型不能转换为日期时间型。

日期时间型转换为时间型，会舍弃日期信息；

time(2020.01.01 13:00:01.001002003)  // 13:00:01.001
minute(2020.01.01 13:00:01)  // 13:00m

时间型转换为日期时间型，会抛出异常。

datetime(13:00:01)  // The function datetime does not support second data

（4）日期型和时间型不能相互转换。

month(13:00:01)  // The function month does not support second data
minute(2020.01.01)  // The function minute does not support date data

3. 常规的时间类型比较

在 DolphinDB 中，常规的时间类型比较通常用于数据量比较小的内存表和流表中，或者单纯的只比较两个时间的大小的向量中，例如：对流数据引擎得到的结果根据需要过滤相应时间段的数据。不同时间类型之间可以使用比较运算符（>, <, >=, <=, ==, !=），in 和 between 进行比较。

使用比较运算符对不同时间类型进行比较时，系统会按照第2章中的转换规则，尝试将时间粒度较粗的类型转换成时间粒度较细的类型，如果能够转换，就作比较；如果不能够转换，则抛出异常。例如，表达式2023.01.04T13:30:10.001 > 2023.01.04 执行时，会将2023.01.04转换成2023.01.04T00:00:00.000再进行比较，因此返回结果是true。

2023.01.04T13:30:10.001 > 2023.01.04   // true
2011.01.01T13:00:00 > 2011.01.02   // false
2023.01.04T13:30:10.001 == 2023.01.04   // false
2023.01.04 == 2023.01.04T00:00:00.000  //  true

需要注意的是，

• MONTH 类型的数据比较特殊，虽然它可以和日期时间型以及同为日期型的 DATE 类型相互转换，但是它们之间不能进行比较。MONTH 只能和MONTH 类型进行比较。
• 日期时间型虽然可以转换为时间型，但是它们之间不能比较。
• 关系运算符 between 和其他比较运算符不同，只有运算符的左右两边的类型一致时，才可以比较。

2023.01.04T13:30:10.001 between 2023.01.04T13:30:10.003:2023.01.04T13:30:10.004   // false

运算符的左右两边的类型不一致时，会报错Temporal data comparison should have the same data type.

 2023.01.04 between 2023.01.04T13:30:10.003:2023.01.04T13:30:10.004
//  between(X, Y). Temporal data comparison should have the same data type.'

4. 时间类型的分区剪枝

在 DolphinDB 的应用实践中，时序数据的时间戳通常会作为分布式数据库的分区列，按照值或者范围分片存储。当查询语句的过滤条件包含分区列时，系统会进行分区剪枝，以减少扫描分区的数量，提升查询性能。了解时间类型的分区剪枝规则能够帮助我们写出高效的 SQL 语句。

在实际使用中，通常会对时间分区列直接进行过滤查询，或者对时间分区列进行显式类型转换后再进行过滤。这两种情况，DolphinDB 的分区剪枝规则略有不同。下面将分开阐述。

下文介绍中将频繁出现三个名称，在此先介绍它们的概念：

• 分区方案类型：database 函数的 partitionScheme 参数指定的数据类型。在这个例子中，分区方案的类型是 [2022.09.01,2022.09.02, 2022.09.03] 的类型，即 DATE 类型：

dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
db = database(dbName, VALUE, [2022.09.01,2022.09.02, 2022.09.03])

• 分区列类型：createPartitionedTable 函数的 partitionColumns 指定的列类型。在这个例子中，分区列的类型是表t的time列的类型，即 DATETIME 类型：

n = 6
t = table(n:n,[`time,`value],[DATETIME,DOUBLE])
t[`time] = [2022.09.01T00:00:00, 2022.09.01T12:00:00, 2022.09.02T00:00:00, 2022.09.02T12:00:00, 2022.09.03T00:00:00, 2022.09.03T12:00:00]
t[`value] = 1..6
pt = db.createPartitionedTable(t, `pt, `time).append!(t)

• 过滤比较中的时间对象。在这个例子中，分布式表的分区列time和数据2022.09.01进行比较，2022.09.01则是过滤比较中的时间对象，是 DATE 类型的数据：

select * from pt where time == 2022.09.01
time                   value
-----------------------------------
2022.09.01T00:00:00    1.00000000
2022.09.01T12:00:00    2.00000000

4.1 对分区列直接过滤

在分布式查询中，当我们使用运算符 <, <=, =, ==, >, >=, in, between 对时间分区列和其他时间类型的数据进行比较时， 比较的规则和在内存表中相同，且系统会进行分区剪枝。但要注意，当分区方案是 DATEHOUR 和 DATETIME 类型时，不支持创建分区表。

下例中，时间列的类型为 DATETIME，按照该列对数据按天进行 VALUE 分区。

dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
	
db = database(dbName, VALUE, [2022.09.01,2022.09.02, 2022.09.03])
n = 6
t = table(n:n,[`time,`value],[DATETIME,DOUBLE])
t[`time] = [2022.09.01T00:00:00, 2022.09.01T12:00:00, 2022.09.02T00:00:00, 2022.09.02T12:00:00, 2022.09.03T00:00:00, 2022.09.03T12:00:00]
t[`value] = 1..6
pt = db.createPartitionedTable(t, `pt, `time).append!(t)

想要查询2022.09.01这一天的数据，可以直接用分区列和2022.09.01进行比较。即使2022.09.01的数据类型和分区列的数据类型不同，依然能够进行分区剪枝，只需扫描2022.09.01这个分区的数据即可。我们可以使用sqlDS 来查看分布式查询拆分子查询的情况。

sqlDS(<select * from pt where time == 2022.09.01>)

想要查询2022.09.01T00:00:00.000这一时刻的数据，也可以直接用分区列和该时刻进行比较，即使两者数据类型不同，依然能进行分区剪枝，只需扫描2022.09.01这个分区的数据即可。

sqlDS(<select * from pt where time == 2022.09.01T00:00:00.000>)

4.2 对分区列进行显式类型转换后过滤

对于显式类型转换的过滤条件中，形如convert_func(col) <operator> constant的表达式，其中convert_func是 date、month 等时间类型转换函数， col是分区列，operator是运算符，constant是比较的值，以下情况可以进行分区剪枝。

4.2.1 使用比较运算符

当<operator>为比较运算符（<, <=, =, ==, >, >=）时，convert_func 返回的数据类型的精度小于等于constant 的精度，并且convert_func 返回的数据类型的精度小于等于分区列的精度，可以进行分区剪枝。

时间精度由低到高的排序为：MONTH < DATE < DATEHOUR < DATETIME < TIMESTAMP < NANOTIMESTAMP

这里以 VALUE 分区为例，分区方案的类型为 DATE，按照 DATE 进行分区创建分布式表：

dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
	
db = database(dbName, VALUE, [2022.09.01,2022.09.30,2022.10.01,2022.10.02,2022.10.31,2022.11.01,2022.11.02,2022.12.31,2023.01.01])
n = 10
t = table(n:n,[`time,`value],[DATE,DOUBLE])
t[`time] = take([2022.09.01,2022.09.30,2022.10.01,2022.10.02,2022.10.31,2022.11.01,2022.11.02,2022.12.31,2023.01.01],n)
t[`value] = rand(100.0,n)
pt = db.createPartitionedTable(t, `pt, `time).append!(t)

想要查询大于某个月的数据时，如果直接用分区列和 MONTH 类型的数据比较，是不支持的；这个时候可以通过month函数，将分区列的类型转换为 MONTH 类型来进行比较。

select * from pt where month(time) > 2022.10M
time          value
------------------------
2022.11.01  15.00570112
2022.11.02  66.54577804
2022.12.31  48.09958597
2023.01.01  50.57664175

因为运算符左右的类型一致，且 MONTH 类型的精度低于分区列 DATE 类型，系统会将小于2022.11M的分区都"剪枝"。

想要查询等于某一时刻的数据时，可以对分区列使用timestamp函数，和 TIMESTAMP 类型的数据进行比较，比如：

select * from pt where timestamp(time) = 2022.09.30T00:00:00.000
time          value
------------------------
2022.09.30  19.33508650

但这个时候由于转换函数的类型 TIMESTAMP 的精度高于分区列 DATE的精度，不满足分区剪枝的条件，虽然可以进行比较，但是不能进行分区剪枝。因此在这种情况下，推荐对分区列直接进行过滤的方式进行比较，效率会更高。

4.2.2 使用 between

当<operator>为between时，convert_func返回的数据类型必须与constant相同，才能进行分区剪枝。

想要查询在某一连续的月的数据，直接将分区列和 MONTH 类型的数据进行比较，是不支持的：

select * from pt where time between month(2022.10M:2022.11M)
// between(X, Y). Temporal data comparison should have the same data type.'

可以对分区列使用month函数，和 MONTH 类型的数据进行比较：

select * from pt where month(time) between month(2022.10M:2022.11M)
time          value
------------------------
2022.10.01  24.45175347
2022.10.02  86.05015869
2022.10.31  78.28769609
2022.11.01  15.00570112
2022.11.02  66.54577804

运算符 between 的左右两边的类型一致，且 MONTH 类型的精度低于分区列 DATE 的精度，满足分区剪枝的条件，系统会从2022.10.01, 2022.10.02, 2022.10.31, 2022.11.01, 2022.11.02 这5个分区中过滤数据，其他的分区会被”剪枝“。

4.2.3 使用 in

当<operator>为 in 时，convert_func 返回的数据类型必须与constant相同，并且constant列表中的连续片段的数量小于16，才能进行分区剪枝。

假设列表为 [2020.01.02, 2020.01.03, 2020.01.04, 2020.01.06, 2020.01.07, 2020.01.12]，那么它包含3个连续片段 2020.01.02..2020.01.04，2020.01.06..2020.01.07，2020.01.12。

想要查询在某一不连续的月的数据，可以使用 month 函数，和 MONTH 类型的数据进行比较：

select * from pt where month(time) in [2022.09M, 2022.11M]
time          value
------------------------
2022.09.01  51.37807030
2022.09.01  50.86722047
2022.09.30  13.91816022
2022.11.01  76.58300183
2022.11.02  74.23354792

运算符 in 的左右两边类型一致，且 MONTH 类型的精度低于分区列 DATE 的精度，[2022.09M, 2022.11M] 是一个不连续的片段，但是片段的数据小于16，满足分区剪枝的条件，系统会从2022.09.01, 2022.09.30, 2022.11.01, 2022.11.02 这4个分区中过滤数据，其他的分区会被”剪枝“。

想要查询在某一连续时刻的数据，可以使用timestamp函数，和 TIMESTAMP 类型的数据进行比较：

select * from pt where timestamp(time) in timestamp(2022.10.31..2022.11.01)
time          value
------------------------
2022.10.31  78.28769609
2022.11.01  15.00570112

虽然运算符 in 的左右两边的类型一致，但因为 TIMESTAMP 的精度高于分区列 DATE 的精度，因此不能进行分区剪枝。

5. 小结

对时间类型进行比较的规则可以分为两类：涉及分区剪枝时的比较和不涉及分区剪枝时的比较。通常时间类型向量比较，内存表查询和分布式表查询中过滤条件为非分区列时的时间类型比较均不涉及分区剪枝。而分布式表查询中过滤条件为分区列时，时间类型比较会涉及分区剪枝。​在实际使用过程中，推荐使用支持分区剪枝的 where 条件来提升查询的效率。尤其针对数据量非常大的分区表进行查询时，分区剪枝能够节省大量时间。