Add DateTime type with milliseconds

[dawid_nawrot]

I work with data where milliseconds is my saviour when I count distinct the datetime to get number of events. Alteryx ignores the millisecond part (as lots of other BI tool providers - I don't know what is going on with this idea that milliseconds are not needed). Yes I can convert it to string but it's not the best practice to create duplicate fields just so that I have date part for date-related calculation (plotting, time difference) and on the other hand string value for quick and easy counting..

[braxtone]

Date/Time parsing in Alteryx is really weak. I'd love to see it support the full ISO 8601 format for dates, including milliseconds and timezones.

[jonriddle]

Agreed, very shocked to see that Alteryx datetime fields don't support milliseconds.  I have many situations where I need to sort by datetime and without the millisecond portion, the sorting is not consistent.  Sure, I can use other fields to ensure the sort works as expected, but I shouldn't have to.

[AltSam]

NEED full date time support including milliseconds and timezones

[SeanAdams]

Adding @Ned @AdamR_AYX

part of the reason that @Ned and team did not add milliseconds is that Alteryx cannot guarantee millisecond accuracy on timestamps generated INSIDE a flow because you cannot guarantee sequencing (when tools operate in parallel).

However if what you're doing is calculating the number of milliseconds between a trade execution, and a trade being cleared - then this is not Alteryx dependant (this is all raw data) and having milliseconds is essential and many people have to find a workaround for this by creating a secondary ms field.

well worth engaging Ned and the technical team in this since this will directly play into the design of the new engine.

[SeanAdams]

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_AYX @BenG @Ned @Deeksha @avinashbonu @revathi @samnelson @JoshKushner @MPistone @Arianna_Fuller @Ari_Fuller @AshleyK 

[Ned]

We will look into it...

[BenG]

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

[SeanAdams]

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 

[TravisH]

I agree that support for milliseconds would be beneficial. I have data that has milliseconds which is a part of my key. If I join in-database I have no issues, however as soon as I bring the data locally the milliseconds appear to be stripped and I can't effectively join locally (I get duplicate entries). I've been able to use a workaround by creating a string field in-database before bringing the data locally, however this would be more difficult to do without In-Database tools (DB2) and seems to be an unnecessary hassle if it would at least bring milliseconds into the flow.

In reference to the time stamp accuracy in a workflow, couldn't the time stamp vary by more than a second anyhow if the workflow took longer to run?

Any future support for milliseconds would be greatly appreciated!!

[wlhale]

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