QueryStartDate and QueryEndDate timezone in TickHistoryTimeAndSalesExtractionRequest condition

kkto

I have been using the TRTH v2 RESTful API for a while to retrieve time and sales data. Recently we found that the data we retrieved no longer match what we requested. Upon investigation, it seems that the QueryStartDate and QueryEndDate in the condition field of the query is now interpreted in local timezone rather than in UTC, despite that we have specified it in the same universal time format as before (i.e., with a "Z" suffix), and it is also what is suggested in the document. The "document" refers to "User Guide for Tick History 11.3 REST API" found at https://developers.thomsonreuters.com/thomson-reuters-tick-history-trth/thomson-reuters-tick-history-trth-rest-api/docs?content=11928&type=documentation_item (see Chapter 8, page 54).

The "Z" suffix is commonly known as the UTC designator (https://www.w3.org/TR/NOTE-datetime), so if the example in the documentation contain that suffix, I expect that the datetime string is specified in UTC. And it actually has been the case before.

Is it a bug introduced in recent version?

FYI, Here is the full request body we sent:

'{"ExtractionRequest": {"@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.TickHistoryTimeAndSalesExtractionRequest", "ContentFieldNames": ["Trade - Price", "Trade - Accumulated Volume", "Trade - Volume", "Trade - Market VWAP", "Trade - Qualifiers", "Quote - Bid Price", "Quote - Bid Size", "Quote - Ask Price", "Quote - Ask Size", "Quote - Qualifiers", "Auction - Price", "Auction - Volume", "Auction - Qualifiers", "Correction - Original Price", "Correction - Original Volume", "Correction - Qualifiers", "Correction - Price", "Correction - Volume", "Quote - 30 Day ATM IV Call", "Quote - 60 Day ATM IV Call", "Quote - 90 Day ATM IV Call", "Quote - 30 Day ATM IV Put", "Quote - 60 Day ATM IV Put", "Quote - 90 Day ATM IV Put", "Market Conditions - Qualifiers", "Correction - Exchange/Contributor ID"], "Condition": {"ApplyCorrectionsAndCancellations": false, "QueryStartDate": "2018-03-13T08:00:00Z", "QueryEndDate": "2018-03-13T09:00:00Z", "ReportDateRangeType": "Range", "SortBy": "SingleByTimestamp", "MessageTimeStampIn": "GmtUtc", "DisplaySourceRIC": true}, "IdentifierList": {"@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList", "InstrumentIdentifiers": [{"Identifier": "0#CL:", "IdentifierType": "ChainRIC"}], "ValidationOptions": {"AllowHistoricalInstruments": true}, "UseUserPreferencesForValidationOptions": false}}}'

And here are what we found in the resulting notes text:

Extraction Services Version 11.3.38483 (495cc9d47079), Built Mar 8 2018 22:15:21
User ID: 9013345
Extraction ID: 2000000021735140
Schedule: 0x061de3616bfb2f96 (ID = 0x0000000000000000)
Input List (1 items): (ID = 0x061de3616bfb2f96) Created: 2018-03-28 09:10:18 Last Modified: 2018-03-28 09:10:18
Report Template (26 fields): _OnD_0x061de3616bfb2f96 (ID = 0x061de36178ab2f96) Created: 2018-03-28 09:05:14 Last Modified: 2018-03-28 09:05:14
Schedule dispatched via message queue (0x061de3616bfb2f96), Data source identifier (67BE977CC50946058D5E1FD88204895C)
Schedule Time: 2018-03-28 09:05:17
Processing started at 2018-03-28 09:05:17
Processing completed successfully at 2018-03-28 09:10:18
Extraction finished at 2018-03-28 01:10:18 UTC, with servers: tm01n01, TRTH (101.855 secs)
Instrument <CHR,0#CL:> expanded to 107 RIC instances: CLF0 to CLZ9.
Quota Message: INFO: Tick History Futures Quota Count Before Extraction: 14; Instruments Approved for Extraction: 1; Tick History Futures Quota Count After Extraction: 14, 41.1764705882353% of Limit; Tick History Futures Quota Limit: 34
Manifest: #RIC,Domain,Start,End,Status,Count
Manifest: CLF0,Market Price,2018-03-13T00:00:30.840897962Z,2018-03-13T00:59:37.979349209Z,Active,191
Manifest: CLF1,Market Price,,,Inactive,0
Manifest: CLF2,Market Price,,,Inactive,0
Manifest: CLF24,Market Price,,,Inactive,0
Manifest: CLF25,Market Price,,,Inactive,0
Manifest: CLF26,Market Price,,,Inactive,0
Manifest: CLF27,Market Price,,,Inactive,0
Manifest: CLF3,Market Price,,,Inactive,0
Manifest: CLF9,Market Price,2018-03-13T00:00:00.297905008Z,2018-03-13T00:59:44.306972932Z,Active,4047
Manifest: CLG0,Market Price,,,Inactive,0
Manifest: CLG1,Market Price,,,Inactive,0
...

Note that while the request is for data between "2018-03-13T08:00:00Z" and "2018-03-13T09:00:00Z", what we actually received are data between 2018-03-13T00:00Z and 2018-03-13T00:59Z.

veerapath.rungruengrayubkul

Hi @kkto,

This could be due to the Data Range Time Zone issue fixed in TRTH REST API 11.3 release. You can refer to this advisory.

In release 11.3 (available on January 8, 2018), the "QueryStartDate" and "QueryEndDate" parameter use the Time Zone set in your user preferences. You can also set the timezone via the DateRangeTimeZone parameter in the request.

Below is the sample value of UTC time, if you want to set the QueryStartDate and QueryEndDate in UTC.

"Condition": {
        ...
	"DateRangeTimeZone": "UTC"
}

kkto

It does fix the problem. Thanks a lot!

May I also ask where is the setting documented, and what are the permissible values of the setting?

kkto

It does fix the problem. Thanks a lot!

May I also ask where is the DateRangeTimeZone setting documented, and what are the permissible values of the setting?

veerapath.rungruengrayubkul

The available parameters are defined in the REST API Reference Tree. The value of the DateRangeTimeZone can be either .Net Timezone ID or .Net Timezone Name. However, some time zone names are unsupported, so the .Net Timezone ID is recommended to be used. I can get the available .Net Timezone IDs in my machine. The list is attached.

For more information:

Release Notes for Tick History 11.1.5.5

Release Notes for Tick History 11.2 timezoneid.zip

kkto

Although I accept the answer, I think we still need an update to the API manual. In particular, if the timestamp is not in UTC, it should not be suffixed with a "Z" character in the example request. The DateRangeTimeZone parameter should also be described somewhere.