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

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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 

10 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 

 

 

Meteor

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.

Alteryx Partner

To do some anti-money-laundering analysis for a big bank, we are going to have to match trade times.  I have had to add a column of double type, next to datetime, to hold the sub-second part of trade timestamps.  It is embarrassing to explain to the client that Alteryx does not do fractions of a second.

 

All languages like C# and C++ have well established datetime types that handle fractions of a second.  If you can't give us a better datetime type, can you at least give us a clock-ticks type so we can work with high precision time data?

 

I love Alteryx, but I would love it so much more with milliseconds and nanoseconds out of the box. 

Meteor

Time is ticking for me at least! Multiple projects requiring high precision time data. Afraid to drop Alteryx for multiple projects and go back to Perl/C# just because of this issue. Could you please provide ETA? 

Alteryx Partner

@jineshnp31 I have managed to work around the missing sub-second part by including a double value that rides along with the DateTime.  The double value holds the sub-second value, and can go to many decimal places.  I do hope that Alteryx can give us an ETA on a time for this change though, just today one of our clients commented on the problem.