Getting distinct dates from a timestamp column

Community
1

Hi, I have a timestamp ‘st’ column which is used as a partition ‘day’ column as well. I am looking to find the distinct dates for when the data was stored. I am looking for something like:

SELECT DISTINCT CAST(st as DATE) as date from table1 order by date;

This still gives me timestamps and also seems to scan the multiple billions of rows across days to respond. Takes couple of minutes to return the query.

Any simple and fast ways to achieve this?

thanks.

2

Maybe select ts from table1 sample by 1d. It will still return a timestamp, as the date and timestamp types both include the time in questdb (the difference being microseconds vs milliseconds resolution).

If your data is partitioned by day, you could always just check the metadata and do
select name from (show partitions from table1)

3

‘sample by 1d’ does the trick but it still takes about 25 sec to respond. The show partitions on table1 is very quick (30ms) but I cannot use that in the query where I need to join with other table or query. Any way I can just extract date portion of the ts and use it for various operations?

4

Hello, would something like this work for you?

with parts as (
  show PARTITIONS from trips
),
dates as (
  select minTimestamp from parts
)
select * from dates;