I've been trying to get near closing prices in NYSE for the stock prices every day for the last month. The trading hours are before 16:00 EST and I want to get prices at 15:30. My simplified schema is

CREATE TABLE "trades" ( 
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY

Is there a way to query the last price before 15:30 every day?

I've been trying to get near closing prices in NYSE for the stock prices every day for the last month. The trading hours are before 16:00 EST and I want to get prices at 15:30. My simplified schema is

CREATE TABLE "trades" ( 
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY

Is there a way to query the last price before 15:30 every day?

• database
• time-series
• questdb

Share Improve this question Follow edited Mar 25 at 11:37 Javier Ramirez 4,08511 gold badge2727 silver badges3636 bronze badges asked Mar 24 at 18:20 Doncarleone512Doncarleone512 40222 silver badges88 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can probably do this to get the latest price per symbol before 15:30 EST every day for the last month. I convert a random date (2025-01-01) with the desired timestamp 15:30:00 from EST to UTC, as QuestDB stores timestamps at UTC. Then I extract the HH:mm:ss component from both this date and the table timestamp column, to get all records below that timestamp. I group by date_trunc('day', timestamp), so I will get a single result per day, then I get the last timestamp and price for each day.

Note for the group by I am not converting to EST first. I am assuming you have frequent entries for each symbol, so there should be data for each of them in the few hours before 15:30 EST always. In this case it is safe to ignore the timezone offset between UTC and EST for teh group by. If some symbols had infrequent values, it might be a good idea to add the timezone conversion to the group by, at the expense of performance.

select  symbol, to_timezone(last(timestamp), 'EST'), last(price) 
from trades
where timestamp > dateadd('M', -1, now()) 
and 
to_str(timestamp, 'HH:mm:ss') < 
to_str(to_utc('2025-01-01T15:30:00', 'EST'), 'HH:mm:ss')
group by date_trunc('day', timestamp), symbol;