How do I convert a DateTime into a BIGINT in Stream Analytics?

Question

I'm trying to convert a DateTime (from System.Timestamp()) into a BIGINT or the string equivalent in Azure Stream Analytics. Unfortunately, the obvious method does not work:

CAST(System.Timestamp() AS BIGINT)

I just get the error:

Cannot cast value of type 'datetime' to type 'bigint' in expression 'CAST (System . Timestamp ( ) AS BIGINT)'.

My end goal is to just get either the ticks equivalent of the timestamp or some sort of formatted output such as yyyyMMddHHmmss that I want to use as a unique id.

Answer 1

You can use the DATEDIFF function, with a difference in microseconds to obtain unique values.

The following code provides a timestamp equivalent to ticks:

DATEDIFF(microsecond, '1970-01-01T00:00:00.0000000Z', System.Timestamp()) AS TicksEquivalent

Output: