CODEWITHSUNDEEP
azure-stream-analytics
Mister Epic
Mister Epic

Reputation: 16723

Generate inverted timestamp in stream analytics query

I want to pipe the output of a stream analytics job to table storage. For the rowkey, I want to use an inverted timestamp. In C# I would simply do the following:

var invertedTimestamp = DateTime.MaxValue.Ticks - DateTime.UtcNow.Ticks;

I am attempting to write a ASA job query, but don't know how to get something analogous:

SELECT
    CONCAT(deviceId, '_', sessionId) as DeviceSession,
    DATEDIFF ( ms , NOW(), MAX_DATE() ) //pseudocode
INTO
    tablestorage
FROM
    iothub

Upvotes: 7

Views: 429

Answers (2)

Syed Danish Haider
Syed Danish Haider

Reputation: 1384

Try the below code, it will work.

Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("MMM dd yyyy HH:mm:ss", 
Locale.ENGLISH);
try 
    {
    String datestart="June 14 2018 16:02:37";
    cal.setTime(sdf.parse(datestart));// all done
     Calendar cal1=Calendar.getInstance();
    String formatted = sdf.format(cal1.getTime());//formatted date as i want
    cal1.setTime(sdf.parse(formatted));// all done
    long msDiff = cal1.getTimeInMillis() - cal.getTimeInMillis();
    long daysDiff = TimeUnit.MILLISECONDS.toDays(msDiff);
    Toast.makeText(this, "days="+daysDiff, Toast.LENGTH_SHORT).show();
    } 
    catch (ParseException e) 
    {
    e.printStackTrace();
    }

Upvotes: 3

wolszakp
wolszakp

Reputation: 1179

You can use User Defined Functions

With function:

/ There are 10000 ticks in a millisecond.
// And there are 621355968000000000 ticks between 1st Jan 0001 and 1st Jan 1970.
function tics_to_max() {
	var convertToTicks = function (date) {
		return ((date.getTime() * 10000) + 621355968000000000);
	};

	var max = new Date('9999-12-31T23:59:59.999Z');
	var maxTics = convertToTicks(max);
	var currentTics = convertToTicks(new Date());

	return maxTics - currentTics;
}

And your query can look like this:

SELECT
  CONCAT(deviceId, '_', sessionId) as DeviceSession,
  udf.tics_to_max() AS TicsToMax
INTO
  tablestorage
FROM
  iothub

Upvotes: 4

Related Questions