Azure Data Factory : querying _ts (epoch) field in

问题:

I am trying to copy data from Azure CosmosDB into Azure SQL Database.

In the Azure CosmosDB source, the query is as follows :

select * from c where c.DefinitionTime >= '@{formatDateTime(pipeline().parameters.windowStart, 'yyyy-MM-ddTHH:mm:ssZ' )}' AND c.DefinitionTime < '@{formatDateTime(pipeline().parameters.windowEnd, 'yyyy-MM-ddTHH:mm:ssZ' )}'

In CosmosDB, "DefinitionTime" is stored as a string. To the run the above query, I have to update "DefinitionTime" with range-indexing which is one possible solution.

Another possible solution to is to use the _ts field which is available on all CosmosDB documents. _ts represents when the document was created or last updated in epoch time.

So basically the question is how to convert pipeline().parameters.windowStart to epoch time. The "formatDateTime" function's second parameter that takes in format doesn't seem to have a way of asking for epoch format. Additionally, there is no way of doing datetime arithmetic to convert to epoch manually.