I am running this query on the QuestDB demo instance at

SELECT timestamp_floor('7d', timestamp) t, count
FROM trades
WHERE timestamp in '2025'
ORDER BY 1;

I am filtering for data only in 2025, and I would expect to get data at 7-day intervals, starting from January 1st. However, if I run this query the first two rows are

2024-12-26T00:00:00.000000Z, 1948979
2025-01-02T00:00:00.000000Z, 20655447

Is this a bug?

I am running this query on the QuestDB demo instance at https://demo.questdb.io

SELECT timestamp_floor('7d', timestamp) t, count
FROM trades
WHERE timestamp in '2025'
ORDER BY 1;

I am filtering for data only in 2025, and I would expect to get data at 7-day intervals, starting from January 1st. However, if I run this query the first two rows are

2024-12-26T00:00:00.000000Z, 1948979
2025-01-02T00:00:00.000000Z, 20655447

Is this a bug?

• database
• time-series
• questdb

Share Improve this question Follow asked Mar 27 at 13:06 Javier RamirezJavier Ramirez 4,08511 gold badge2727 silver badges3636 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

timestamp_floor by defaults calculate intervals starting at the EPOC 1970-01-01, which was a Thursday. If you go from the EPOCH in 7 days intervals, you would find 2025-01-01 falls in the 7d bucket starting at 2024-12-26. You can however override the reference offset for timestamp_floor passing an extra param:

SELECT timestamp_floor('7d', timestamp, '2025-01-01') t, count
FROM trades
WHERE timestamp in '2025'
ORDER BY 1;

This returns as the first two rows

2025-01-01T00:00:00.000000Z,18589788
2025-01-08T00:00:00.000000Z,21018319

Which is the intuitively expected result