As Of (Prevailing)

Exact Time Match

Data at a given timestamp can be retrieved by specifying TIMESTAMP = the specified timestamp.

Retrieve Records at specified Timestamp

SELECT * FROM LSE.TRD
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP = '2024-01-03 08:22:51.848 UTC'
LIMIT 10

Retrieve Records at specified Timestamp results

Symbol	Timestamp	PRICE	SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	EXCH_TIME	TRADE_ID	TRADE_TYPE	TRADE_VENUE	PUB_VENUE	TRADE_CURRENCY	MMT_MKT_MECH	MMT_TRD_MODE	MMT_TRANS_CAT	MMT_NEGOTIATED_IND	MMT_CROSS_IND	MMT_MOD_IND	MMT_BENCHMARK_IND	MMT_DIVIDEND_IND	MMT_OFF_BOOK_AUTO_IND	MMT_PRICE_FORMING_IND	MMT_ALGO_IND	MMT_PUB_MODE	MMT_DEFERRAL_TYPE	MMT_DUP_IND	DELETED_TIME	TICK_STATUS
LSE::VOD	2024-01-03 08:22:50.847	0.818	200	LSE::VOD	TRD	1	2024-01-03 08:22:50.763	427637175188410480	OB	SINT	ECEU	EUR	4	7								P						0

Most Recent / Prevailing Value

To return the most recent / prevailing value as of the specified time, additionally define a lookback period init_lookback in seconds.

Retrieve the most recent trade as of the specified Timestamp

SELECT * FROM LSE.TRD t
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP = '2024-01-03 08:22:51.848 UTC'
and t.init_lookback = 86400
LIMIT 10

Retrieve the most recent trade as of the specified Timestamp results

Symbol	Timestamp	PRICE	SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	EXCH_TIME	TRADE_ID	TRADE_TYPE	TRADE_VENUE	PUB_VENUE	TRADE_CURRENCY	MMT_MKT_MECH	MMT_TRD_MODE	MMT_TRANS_CAT	MMT_NEGOTIATED_IND	MMT_CROSS_IND	MMT_MOD_IND	MMT_BENCHMARK_IND	MMT_DIVIDEND_IND	MMT_OFF_BOOK_AUTO_IND	MMT_PRICE_FORMING_IND	MMT_ALGO_IND	MMT_PUB_MODE	MMT_DEFERRAL_TYPE	MMT_DUP_IND	DELETED_TIME	TICK_STATUS
LSE::VOD	2024-01-03 08:22:51.848	0.818	200	LSE::VOD	TRD	1	2024-01-03 08:22:50.763	427637175188410480	OB	SINT	ECEU	EUR	4	7								P						0

As Of Join

To retrieve trades joined to prevailing quotes an asof join is specified using the sametime_as_existing function, specifying the trade timestamp as the leading source, followed by the quote timestamp.

As of Join, Joining Trades to Prevailing Quotes

SELECT t.PRICE as PRICE,t.SIZE as SIZE,
q.TIMESTAMP QTE_TIME, q.BID_PRICE as BID_PRICE, q.ASK_PRICE as ASK_PRICE
FROM LSE_SAMPLE.TRD t, LSE_SAMPLE.QTE q
WHERE t.SYMBOL_NAME='VOD' and q.SYMBOL_NAME='VOD'
and sametime_as_existing(t.timestamp, q.timestamp, 0) = TRUE
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

As of Join, Joining Trades to Prevailing Quotes results

Symbol	Timestamp	PRICE	SIZE	BID_PRICE	ASK_PRICE	QTE_TIME
LSE_SAMPLE::VOD	2024-01-03 07:15:10.133	69.76	40,000	69	71.82	2024-01-03 05:00:07.876
LSE_SAMPLE::VOD	2024-01-03 08:00:06.232	70	184,613	70.01	70.19	2024-01-03 08:00:06.226
LSE_SAMPLE::VOD	2024-01-03 08:00:06.233	70.01	140	70.01	70.19	2024-01-03 08:00:06.226
LSE_SAMPLE::VOD	2024-01-03 08:00:06.287	70.01	500	70.01	70.19	2024-01-03 08:00:06.287
LSE_SAMPLE::VOD	2024-01-03 08:00:08.113	70.104	2,800	70.08	70.18	2024-01-03 08:00:07.747
LSE_SAMPLE::VOD	2024-01-03 08:00:09.380	70.137	49	70.08	70.18	2024-01-03 08:00:08.234
LSE_SAMPLE::VOD	2024-01-03 08:00:09.943	70.0981	66,908	70.08	70.18	2024-01-03 08:00:08.234
LSE_SAMPLE::VOD	2024-01-03 08:00:09.944	70.1097	214	70.08	70.18	2024-01-03 08:00:08.234
LSE_SAMPLE::VOD	2024-01-03 08:00:10.119	70.146	840	70.08	70.18	2024-01-03 08:00:08.234
LSE_SAMPLE::VOD	2024-01-03 08:00:10.933	70.132	344	70.08	70.18	2024-01-03 08:00:08.234