How to convert datetime to epoch time when add the dynamic content in Azure Synapse Analytics

Question

Hello,

I am now trying to create a linked service to copy data from external REST API looks like below:
https://services.XXX.com/api/XXX/my-project/XXX?from=**

Answer 1

Hello @Zhou Wentong ,

Thanks for the question and using MS Q&A platform.

In order to convert a timestamp to epoch time using ADF dynamic expression (not in mapping data flow):

• You can utilize the ticks() function to convert the given timestamp to ticks value
• Then convert that ticks value to seconds (10 million ticks = 1 second), nothing but divide the ticks value by 100000000 to get the seconds value. Lets say this as Value_A
• In the same way convert 1970-01-01T00:00:00.000 to seconds using same math calculations. Lets say this value as Value_B
• Now subtract Value_A - Value_B which will give the epoch time of a given timestamp

To calculate the below timestamp,

The dynamic expression will be as follows:

@string(sub(div(ticks(pipeline().parameters.param_currenttimestamp),10000000),div(ticks(pipeline().parameters.param_epoch),10000000)))  

I have used this calculator website to verify the results: https://www.epochconverter.com/

Below is the Dynamic expression output value:

Here is the pipeline JSON code just in case if you would want to play around as per your requirement.

{  
    "name": "pl_ConvertCurrentTimeToEpochTime",  
    "properties": {  
        "description": "Convert Current Timestamp to Epoch time",  
        "activities": [  
            {  
                "name": "Set variable1",  
                "type": "SetVariable",  
                "dependsOn": [],  
                "userProperties": [],  
                "typeProperties": {  
                    "variableName": "outValue",  
                    "value": {  
                        "value": "@string(sub(div(ticks(pipeline().parameters.param_currenttimestamp),10000000),div(ticks(pipeline().parameters.param_epoch),10000000)))",  
                        "type": "Expression"  
                    }  
                }  
            }  
        ],  
        "parameters": {  
            "param_epoch": {  
                "type": "string",  
                "defaultValue": "1970-01-01T00:00:00.000"  
            },  
            "param_currenttimestamp": {  
                "type": "string",  
                "defaultValue": "2022-06-30T00:00:00.000"  
            }  
        },  
        "variables": {  
            "outValue": {  
                "type": "String"  
            }  
        },  
        "annotations": []  
    }  
}  

Hope this will help.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

toInteger(toLong(rtrim(toString((currentUTC('GMT'))-toTimestamp('1970-01-01T00:00:00','yyyy-MM-dd'T'HH:mm:ss', 'GMT')),'')) / 1000)