We use Refinitiv RTO data for global strategies, so we need last trade and quote times in UTC. The SALTIM and QUOTIM fields are in UTC, but unfortunately, the TRADE_DATE and QUOTE_DATE fields are not UTC. They represent the "trading date," so for exchanges that are open across UTC midnight, these date fields roll over before or after UTC midnight. This means that if I try to build a UTC timestamp using TRADE_DATE and SALTIM, I get the incorrect date for a period of time each day.
For example, consider the CME RIC ESZ2 (CONTEXT_ID 1118, XMIC_CODE XCME) on 2022-10-18. The exchange is open 23 hours a day with a 1-hour trading closure from 4 to 5 PM Central (21:00 to 22:00 UTC during DST). Quote reporting starts at 4:45 and the exchange reopens at 5 PM. QUOTE_DATE and TRADE_DATE roll over to 2022-10-19 on the first quote and trade after the closure at 4:00. However, the correct UTC date is still 2022-10-18. So, for example, the first trade with TRADE_DATE=2022-10-19 has SALTIM_NS=22:00:00, but this trade actually happened on 2022-10-18.
To compute the correct UTC timestamp it appears that I need to know the close time of every exchange for every day. Then I subtract one day from TRADE_DATE and QUOTE_DATE for trades and quotes with TIME between market open and UTC midnight. This is extremely cumbersome and error prone. There must be a better solution. How are other Refinitiv RTO users dealing with this problem?
Some options I have considered:
* Use ACTIV_DATE instead of TRADE_DATE. This works for trades on many exchanges, but does not work for quotes. ACTIV_DATE rolls over on the first trade after UTC midnight, so gives the wrong date for all quotes that arrive after market close and before UTC midnight. This is what I'm doing currently, but now I need the quote data
* Ignore date fields in the message and guess it using the system clock. This is heuristic and error-prone. You can't reprocess archived messages for old dates and you are not guaranteed to get the correct date for the last trade/quote dates computed from the refresh message on connect
* Store market hours and adjust TRADE/QUOTE dates. Maintaining market hours for all exchanges is a huge undertaking. Does Refinitiv provide this data queryable by CONTEXT_ID so I can look it up?
* Other date fields suggested here. None of these roll at UTC midnight for the CME case I mentioned above
