Issue while selecting messages in Reverse Order along with 'Over Time' function

[bardale]

Hello,
Greetings
I am reading messages in reverse chronological order for every specific time interval.

Below are queries, I am hitting via Timebase shell.

1. select * from order_book_depth over TIME(1m) where timestamp < '2022-08-25 05:46:10.999'd
   This query is working perfectly and returning messages as per expectation.

2. select * from REVERSE(order_book_depth) over TIME(1m) where timestamp < '2022-08-25 05:46:10.999'd
   When I read messages in reverse chronological order then its always giving single message even though there are enough messages in Timebase.

Below is our original query which we are hitting to get messages in reverse chronological order which are rolled up over 1 min.

Working query without REVERSE:
elect last{} (bid_size/1000000) as bid_size, last{} (ask_size/1000000) as ask_size, last{} (bid_price), last{} (ask_price), last{} (bid_count), last{} (ask_count), timestamp - (timestamp AS INT64 % 60000) as timestampFloor, timestamp as timestampOg, level from order_book_depth over TIME (1m) where timestamp >= 1661402770000 AND SYMBOL IN ('EURUSD') AND level==1 GROUP BY level limit 20

Non working query with REVERSE:
select last{} (bid_size/1000000) as bid_size, last{} (ask_size/1000000) as ask_size, last{} (bid_price), last{} (ask_price), last{} (bid_count), last{} (ask_count), timestamp - (timestamp AS INT64 % 60000) as timestampFloor, timestamp as timestampOg, level from REVERSE(order_book_depth) over TIME (1m) where timestamp <= 1661402770000 AND SYMBOL IN ('EURUSD') AND level==1 GROUP BY level limit 50

Could you please help to resolve above issue?

[alex-karpovich]

I will push a fix today

[alex-karpovich]

@bardale fixed in 6.1.18
https://github.com/finos/TimeBase-CE/releases/tag/6.1.18

[bardale]

Thank you @alex-karpovich for quick fix.

[bardale]

Hi @alex-karpovich
Greetings
Reverse is working properly with version 6.1.18.

When I execute below query on Timebase shell then query returning messages with first record over specific time interval (ex: 1Min) instead of last.
Please find below query and result.
==> select last{} (bid_price) as bid_price, timestamp as timestampOg, level from REVERSE(order_book_depth) over TIME (1m) where timestamp < 1661411100000 AND SYMBOL IN ('EURUSD') GROUP BY level limit 20

_,TIMESTAMP,SYMBOL,TYPE,BID_PRICE,TIMESTAMPOG,level
0,2022-08-25 07:04:00,EURUSD,1.0008,2022-08-25 07:04:00.020,1
1,2022-08-25 07:04:00,EURUSD,1.00075,2022-08-25 07:04:00.020,2
2,2022-08-25 07:04:00,EURUSD,1.0007,2022-08-25 07:04:00.020,3
3,2022-08-25 07:04:00,EURUSD,1.00065,2022-08-25 07:04:00.020,4
4,2022-08-25 07:04:00,EURUSD,1.0006,2022-08-25 07:04:00.020,5
5,2022-08-25 07:03:00,EURUSD,1.00055,2022-08-25 07:03:00.185,1
6,2022-08-25 07:03:00,EURUSD,1.0005,2022-08-25 07:03:00.185,2
7,2022-08-25 07:03:00,EURUSD,1.00045,2022-08-25 07:03:00.185,3
8,2022-08-25 07:03:00,EURUSD,1.0004,2022-08-25 07:03:00.185,4
9,2022-08-25 07:03:00,EURUSD,1.00035,2022-08-25 07:03:00.185,5

If we consider first record
0,2022-08-25 07:04:00,EURUSD,1.0008,2022-08-25 07:04:00.020,1
There are message with timestamp greater than 07:04:00.020,1 for that particular minute but instead of picking last its picking first record.
I tried first() aggregatition function but still getting same result.

select first{} (bid_price) as bid_price, timestamp as timestampOg, level from REVERSE(order_book_depth) over TIME (1m) where timestamp < 1661411100000 AND SYMBOL IN ('EURUSD') GROUP BY level limit 20

Could you please help?

[alex-karpovich]

@bardale

instead of using
timestamp as timestampOg you have to use
last{} (timestamp) as timestampOg or first{} (timestamp) as timestampOg

[bardale]

Thanks @alex-karpovich