filtering DocumentDb collection from Data Factory using unix-timestamp

Question

I am trying to select some documents from documentDB collection, in an incremental way, so every slice will select based on the "timeCreated" field of the collection. The problem is that this field (timeCreated) is in seconds since the epoch (1970-01-01) and I could not find the proper format here.

As project's assumptions, we are working with Azure Portal and without any programming interface, so the only solution I could think of is creating UDF in the DocumentDB that will transform the seconds field, to a dateTime field, but any approach that will only involve documentDB sql is much better.

This is the date data in the documentDB:

"serverTimestamp": {
      "$date": 1446130451707
    },

This is the way to use slice's startDate and endDate in the pipeline (from Azure documentation) :

"source": {
    "type": "SqlSource",
    "sqlReaderQuery": "$$Text.Format('select * from MyTable where timestampcolumn >= \\'{0:yyyy-MM-dd HH:mm\\' AND timestampcolumn < \\'{1:yyyy-MM-dd HH:mm\\'', WindowStart, WindowEnd)"
},

Is there another way, besides UDF, to format WindowStart/WindowEnd to seconds?

Thanks!

Tom Sun - MSFT Tom Sun - MSFT · Accepted Answer · 2017-05-03T07:11:55

Is there another way, besides UDF, to format WindowStart/WindowEnd to seconds?

As you mentioned we can do that easliy with UDF function. Then we can could use the function in the sql. As DocumentDB now supports range indexes on both strings and numbers. In my option we can format the field that we want to filter instead of format WindowStart/WindowEnd to seconds. The following is the detail test steps:

1.we need to set up the indexes correctly for it to work.

2.Create UDF from with Azure portal

function epochToDate (ts) { 
   return new Date(ts*1000);
}

Note: ts vaule is seconds, so need to covert to milliseconds

3.valid it from the Azure Data factory copydata Wizard

filtering DocumentDb collection from Data Factory using unix-timestamp

1 Answers