1

I have a table in a database partitioned by DATE and SYMBOL, and the DATE column is of the TIMESTAMP type.

Is there any faster way to return all the dates in the table than the statements select distinct(date(datetime)) from t and select count(*) from t group by date(datetime)?

Eva Gao
  • 402
  • 1
  • 7

1 Answers1

2

Method 1:

Query the date partitions by schema(db).partitionSchema. This method is only applicable to the situation where the range of the required dates is not less than the date range specified by the partition scheme.

For example, the partition scheme specifies a database("",VALUE,2020.01.01..2020.01.10) with a ten-day range. If you only append data of one day in a partitioned table within the database with schema(db).partitionSchema, the result is still the ten days. If you append data of 2020.01.01~2020.01.15 (fifteen days), you can obtain all the 15 days.

Method 2:

The first level of database is partitioned by DATE. You can get the partition metadata by the function getTabletsMeta, and then get the results in temporal data types by the function temporalParse.

For more instructions of the function, refer to getTabletsMeta and temporalParse.

For example, there is a database “SH_TSDB_tick” using DATE and HASH partitioning. To get all the dates of table “tick”, you can write the following script:

dbName = "dfs://SH_TSDB_tick"
tbName = "tick"
result = exec dfsPath from getTabletsMeta(dbName[5:]+"/%", tbName) where rowNum != 0
dateList = substr(result, regexFind(result, "[0-9]{8}"), 8).distinct().temporalParse("yyyyMMdd").sort()
Smalllpotato
  • 241
  • 4