community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Seconds; milliseconds & Nanoseconds are now critical need

Hi Ned & Adam,

 

We've had a few discussions about extending the date-time type in Alteryx and this is now critical with the introduction of new regulatory legislation in Europe covering trading of Derivatives products.   The regulation is called MiFID 2, and one of the key requirements is that financial firms report their trades down to the millisecond accuracy.   For Alteryx this is key because much of our data uses Alteryx as a transportation or a data preparation channel - and right now the DateTime field cannot store this level of accuracy.

 

Please can you put this on the roadmap for delivery relatively soon - MiFID 2 is going to hit every bank that performs this kid of business, and all banks will be required to meet this - so if Alteryx cannot carry and compare date-times in full, then this will be a direct competitive disadvantage for the platform and in many cases a critical blocker.

https://qa-financial.com/news/testing/time-stamping-trades-mifid-ii/

 

The best approach would be to follow Microsoft's approach with SQL server - where the DateTime2 type allows 7 digits of precision on dates by default but this can be adjusted - in addition SQL now offers a DateTimeOffset type which includes the timezone and offset information.    This would directly solve the problem without breaking existing flows.

 

 

cc: @AdamR @BenG @Ned @Deeksha @avinashbonu @revathi @samnelson @JoshKushner @MPistone @Arianna_Fuller @Ari_Fuller @AshleyK 

7 Comments
Founder
Founder

We will look into it...

Alteryx Alumni (Retired)
Status changed to: Under Review

Hi @SeanAdams,

 

This information can be stored in a string format and compared the same way other DateTimes are compared.  

 

- Do you need to do DateTime math with this level of accuracy?

- Would having a DateTime.Now() function that returned this level of accuracy give you the functionality you need?

- What other operations would be required?

 

Thanks,
Ben

Aurora
Aurora

Hi @BenG

 

We definitely do need to do this level of accuracy - as mentioned in idea post, several regulations in our industry require us to report on transactions which have taken more than a certain number of miliseconds (or nanoseconds) to commit - so the key here is to be able to take trade records; and booking records and to compare them to spot differences.    While having DateTime.Now() carry this accuracy would be a good start - that's not really the issue that we're facing.   The issue is that our trade records and our booking records are now required by law to carry 5 decimals of accuracy after the second - so we need to be able to read fully-formed date-times out of a SQL server (which by default carries 5 decimals after the second on a DateTime2) or a download file or some other data source.

 

Now you can do this in strings, with all the additional work-around effort of splitting dates into date components etc - so if we're going to all that effort then there's a good argument for not using Ateryx for this use case which would be unfortunate.   The major premise and marketing factor of Alteryx is that you take out this kind of low-value data-prep work (like fixing date-times) so that you can get to the insight quickly.

 

The right way forward here in my my mind, is to revamp DateTime and date completely.   

- Date-times are currently stored in strings in Alteryx which seems somewhat archaic.    Most other tools store date-times in a numeric type because of the significantly reduced space needs.

- while doing this - make the precision on DAteTimes an option (like SQL server does)

- and then clean up the multiple different ways that people have of addressing date-parts across all the different formule and tools - some use magic codes that you have to look up, the date-time parse tool uses more standard codes etc.

 

 

Given that we're rebuilding the core Alteryx engine - this is the time to fix the date-time type - get rid of string-based dates, and move to numeric dates with flexible precision like DateTime2 in SQL (which adopted this more accurate date-time format in 2008)

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql 

 

 

Meteoroid

I ran into this today and came on here to research. We are pulling health care information (observations/test) results and they all contain datetime fields to the level of HH MM SS.999

They are stored this way in the database and I was pulling a MAX(observation date) for comparison. since the datetime was truncated to seconds I was getting multiple rows when I should've only gotten one. I found another way, and probably better in this case, of pulling the latest observation however I decided to followup on this as I'm sure it will come up again. That is how I found this thread/Idea. Thanks, Bill

Atom

HI @Ned.   Any updates as to if this has made the release roadmap?  This is important to us as well.

 

Cheers,

Tim

 

Alteryx Partner

I would vote for this.  Millisecond level timestamps are important in trading to establish the relative order of trades.  It is not the value itself that matters, it is the time relative to other actions.  Also, if you are adding formats, ticks would be useful, as this might give us the maximum possible time granularity.

Meteor

Any updates coming to provide this in 2019 version? This is critical item for us now specially in the Finance and Trading processes and we really want to avoid building anything outside Alteryx just because of the millisecond missing.