First_value should support other datatypes, specifically timestamp

[javier]

Is your feature request related to a problem?

first_value OVER() is a great way of getting LAG functionality, but as of today we support only the Double data type. While this is good, a very common use case for LAG is elapsed time, and QuestDB is after all a time-series database. I would expect to be able to find elapsed time using a window function.

Describe the solution you'd like.

Right now I have to do something like this to find elapsed_time. I cast timestamp to long and back. It is ugly and probably not too efficient

WITH time_and_prev AS (
  SELECT timestamp, symbol, first_value(timestamp::long) OVER (PARTITION BY symbol ORDER BY timestamp ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS prevTimestamp 
FROM trades where timestamp IN '2024-04'
)
SELECT timestamp, symbol, prevTimestamp::timestamp, datediff('s', timestamp, prevTimestamp::timestamp) as gap_s, (timestamp - prevTimestamp::timestamp) / 1000 as gap_ms FROM time_and_prev 
limit -50;

I would like to use first_value(timestamp) and that's it, as in

WITH time_and_prev AS (
  SELECT timestamp, symbol, first_value(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS prevTimestamp 
FROM trades where timestamp IN '2024-04'
)
SELECT timestamp, symbol, prevTimestamp, datediff('s', timestamp, prevTimestamp) as gap_s, (timestamp - prevTimestamp) / 1000 as gap_ms FROM time_and_prev 
limit -50;

For what is worth, I think it would also make a lot of sense for Symbol to be supported. Imagine you have a 'Status' column and want to check when it changed value. By having first_value(symbol_column) you could do that easily.

Describe alternatives you've considered.

As explained above, I can always cast from timestamp to long, it is just ugly

Full Name:

Javier Ramirez

Affiliation:

QuestDB

Additional context

No response