How to use EPOCH (Unix Time in milliseconds of 13 digit characters) in REST API call with date(s)

[Anonymous]

Hi all,

For the last week, I have been trying to call a rest api with epoch or unix time (milliseconds 13 digits) in my rest api calls but I could not find any that I can reuse or refer to. Hopeful, this post helps who are seeking such solutions.

Regards,

Anand

let

//Define the endDate of the query by converting system date time to EPOCH
    UTCDate = Duration.TotalSeconds(DateTimeZone.UtcNow()- #datetimezone(1970,1,1,0,0,0,0,0)),
    ConvertUTCDate = Text.From(UTCDate),
    RemoveDelimiter = Text.BeforeDelimiter(ConvertUTCDate,"."),
    PrefixEpoch = (RemoveDelimiter&"000"),
    EndEPOCH = PrefixEpoch,

//Define the startDate of the query by converting system date time to EPOCH
    
    PreviousUTC = DateTimeZone.From(Number.From(DateTimeZone.UtcNow())),
    PreviousUTCDate = Date.AddDays(PreviousUTC,-X), //replace -x with days e.g., 60 days prior as startdate
    PreviousUTCDateEpoch = Duration.TotalSeconds(PreviousUTCDate - #datetimezone(1970,1,1,0,0,0,0,0)),
    ConvertPreviousUTCDate = Text.From(PreviousUTCDateEpoch),
    RemoveDelimiterPreviousUTCDate = Text.BeforeDelimiter(ConvertPreviousUTCDate,"."),
    PrefixEpochPreviousUTCDate = (RemoveDelimiterPreviousUTCDate&"000"),
    StartEPOCH = PrefixEpochPreviousUTCDate,

//Construct the api call with start and end date from earlier step as text as numeric values are not considered for call by default
    Custom2 = "SearchURL?QueryID=ID&startDate="& StartEPOCH,
    Custom3 = Custom2 & "&endDate=",
    Custom4 = Custom3 & EndEPOCH,
//this is the query that will used to get data using API
    APIcall = Custom4,

    
//App Details
    client_id = #"API Key", //create parameter to define API Key value
    client_secret = #"API Secret", //create parameter to define API secret value
    
//Authentication
    
//URI's
    token_uri = #"Token URL", //create parameter to define Token URL value
    resource =  #"Event URL", //create parameter to define Event URL value
    
    //User Details  
    username = username, //create parameter to define username
    password = password, //create parameter to define password
      
//response token generation with the above steps and parameters

   tokenResponse = Json.Document(Web.Contents(token_uri,          
                                [
                                    Content=
                                    Text.ToBinary(Uri.BuildQueryString(
                                    [
                                        client_id = client_id
                                        ,client_secret=client_secret
                                        ,username=username
                                        ,password=password
                                        ,resource=resource
                                        ,grant_type = "password"
                                        
                                    ]))
                                    ,Headers=
                                        [Accept="application/json"]
                                        , ManualStatusHandling={400}
                                ])),
                                access_token = tokenResponse[access_token],
    token = "Bearer " & tokenResponse[access_token],

//call rest api with the above created parameters i.e., start, end date converted in EPOCH format with REST API for Events

 GetJsonQuery = Json.Document(Web.Contents(Custom4, 
                                                [Headers=
                                                    [Authorization=token]
                                                    ]
                                        )
                            )
in

 GetJsonQuery

[v-juanli-msft]

Hi @Anonymous 

It seems you need to convert datetime format to epoch or unix time(milliseconds), please see methods how to convert datetime to seconds and then use seconds *1000 to get milliseconds.

https://social.technet.microsoft.com/Forums/en-US/64b96320-1872-44d8-931f-68749914bf6c/how-to-convert-datetime-to-unix-time?forum=powerquery

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @v-juanli-msft ,

Thank you for sharing the article but my message was to communicate how to do when you there is a need to convert system time to EPOCH time format without decimal, 13 digit milliseconds and use it part of the REST API.

The query shared by me works for me and it was shared for members seeking solution.

Kind regards,

Anand

[edhans]

Thanks for sharing the info @Anonymous - I marked your reply as a solution so others will recognized it is solved and has a workable answer.