Got a recipe for converting a unix timestamp to a utc date

[Binaryjam]

I have a json response that contains a date in unix timestamp format, anyone know how to convert it?

I tried converttoutc but it throws errors and I cant save the expression.

If that function doesnt work, anyone have a flow recipe to do this ?  Is it possible ?

[v-yuazh-msft]

Hi @Binaryjam,

I have made a test on my side and I afraid that there is no any way to convert a unix timestamp to a utc date in microsoft flow currently.

Converting a unix timestamp to a utc date is not supported in microsoft flow.

I afraid that there is no way to achieve your needs in Microsoft Flow currently.

If you would like this feature to convert a unix timestamp to a utc date to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Best regards,

Alice

[GabrielStJohn]

Hello, @Binaryjam!

Have you had an opportunity to apply @v-yuazh-msft's recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager

[DamnThisRegistr · Accepted solution]

Yeh I reviewed it,  its wrong,  its totally possible to do this in flow, its just a bit of maths and thats all.

addseconds('1970-1-1', Div(timestamp,1000) , 'yyyy-MM-dd')

I figured it out eventually. 

[Dalrymps]

Any recommendations as to how you would then convert this into a different time zone?

[cotton_gin1]

Here is a good example I was able to make use of based on my timestamp:

"last_occurrence_timestamp": 1550573979

Convert this time to '2019-02-19 10:59:39' by using the following calculation

addseconds('1970-1-1', Div(triggerBody()?['data']?['item']?['last_occurrence_timestamp'],1),'yyyy-MM-dd hh:mm:ss')

[ericonline]

Hi @cotton_gin1 , 

What Action do you use? I have a Unix timestamp as output from a Parse JSON Action but which Action do I use to convert using addseconds?

Thanks!

[cotton_gin1]

@ericonline,

It could be a Compose action to call this out for validation but you could use Set Variable as well. Any place where you are using Dynamic Content, you can switch to using an Expression.

[ericonline]

Perfect, thank you!

[ericonline]

Hi @cotton_gin1 .

Hm. I cannot seem to get this working using the recipe you provided (edited for my use case of course). 

Can you identify what I'm missing?

Trigger: Button

Action1: HTTP Request
Action2: Parse JSON

• Here is where I get the Unix timestamp in a `time` parameter
• Example output:

  • {
      "type": "FeatureCollection",
      "metadata": {
        "generated": 1557358894000,
        "url": "https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2019-05-07T00%3a00%3a00.0000000&endtime=5%2f8%2f2019&latitude=39.0422528&longitude=-95.6719104&maxradiuskm=1000&orderby=magnitude-asc",
        "title": "USGS Earthquakes",
        "status": 200,
        "api": "1.8.1",
        "count": 1
      },
      "features": [
        {
          "type": "Feature",
          "properties": {
            "mag": 2.9,
            "place": "16km N of Snyder, Texas",
            "time": 1557260837307,
            "updated": 1557331308692,
            "tz": -360,
            "url": "https://earthquake.usgs.gov/earthquakes/eventpage/us70003i1i",
            "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=us70003i1i&format=geojson",
            "felt": 1,
            "cdi": 2.7,
            "mmi": null,
            "alert": null,
            "status": "reviewed",
            "tsunami": 0,
            "sig": 130,
            "net": "us",
            "code": "70003i1i",
            "ids": ",us70003i1i,",
            "sources": ",us,",
            "types": ",dyfi,geoserve,origin,phase-data,",
            "nst": null,
            "dmin": 0.028,
            "rms": 0.48,
            "gap": 36,
            "magType": "mb_lg",
            "type": "earthquake",
            "title": "M 2.9 - 16km N of Snyder, Texas"
          },
          "geometry": {
            "type": "Point",
            "coordinates": [
              -100.9142,
              32.8648,
              1.96
            ]
          },
          "id": "us70003i1i"
        }
      ]
    }

Action3: Apply to Each

• Using the Parse JSON output
• Action4: Compose
  
  • I've tried the following 3 combinations to convert the Unix timestamp to no avail:

  • **Your Recipe:** `addseconds('1970-1-1', Div(triggerBody()?['data']?['item']?['last_occurrence_timestamp'],1),'yyyy-MM-dd hh:mm:ss')`
    
    **Test 1:**   `addseconds('1970-1-1', items('Apply_to_each')?['properties']?['time'],'yyyy-MM-dd hh:mm:ss')`
    **RESULTS:** `InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2590': 'Value to add was out of range.
    Parameter name: value'.`
    
    **Test 2:**  `addseconds('1970-1-1', Div(items('Apply_to_each')?['properties']?['time'],1),'yyyy-MM-dd hh:mm:ss')`
    **RESULTS:** `InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2590': 'Value to add was out of range.
    Parameter name: value'.`
    
    **Test 3:**  `addseconds('1970-1-1', item()?['properties']?['time'],'yyyy-MM-dd hh:mm:ss')`
    **RESULTS:** `InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2590': 'Value to add was out of range.
    Parameter name: value'.`

• I've also tried Compose, Set Variable, and Initialize Variable Actions before the Apply to Each action. No Go.

Any ideas? 
Thank you!

[cotton_gin1]

@ericonline 

That time field listed in your JSON is different than the one I was using. 

Change your formula to divide by 1000.

`addseconds('1970-1-1', Div(triggerBody()?['data']?['item']?['last_occurrence_timestamp'],1000),'yyyy-MM-dd hh:mm:ss')`

That should work.

[ericonline]

@cotton_gin1 , thank you. Worked like a charm!

[ericonline]

Hi @cotton_gin1 ,

Do you have a recipe for converting FROM a UTC timestamp TO Unix time?

Example: 

I need to query an API using HTTP Action. The query requires a Unix timestamp format. I'm thinking of using Flow Get Current Time action, then wrapping it using a Compose Action. Think this will work?

Need to convert:

FROM: 2019-07-18T09:00:00-08:00 (or 2019-07-18T09:00:00Z)

TO: 1563440400

**EDIT** Found the answer here! https://powerusers.microsoft.com/t5/Flow-Ideas/Convert-Tima-Date-format-to-UNIX-Timestamp/idi-p/1457...

[PanooneHDA]

@cotton_gin1 , can you please describe your complete working flow?

When I test my flow, the Compose action is showing the Output is the same as the Input! Are any parts of the expression meant to use dynamic variables?

Using:

addseconds('1970-1-1', Div(items('Apply_to_each')?['properties']?['timestamp'],1000),'yyyy-MM-dd hh:mm:ss')

[ericonline]

Hi @PanooneHDA ,

You will need to specify the timestamp field within your Flow.

In your example: `addseconds('1970-1-1', Div(items('Apply_to_each')?['properties']?['timestamp'],1000),'yyyy-MM-dd hh:mm:ss')` expression, the `items('Apply_to_each)` refers to the "For Each" loop, `['properties']?['timestamp']` refers to the specific field to convert. 

You'll need to "dig" into your own data structure to convert the timestamp.

[juresti]

@ericonline 

I don't believe the formula addseconds is the issue.

You have to figure out how to access the "time" field.

First you need to test until you access "features", it could be item()?['features'] or it could be using an index item()[0]

I always have to test for nested json since I forget when to use index and when to use property name.

Once you access "features" then you figure out how to access "time" field.

[juresti]

@cotton_gin1  it did not work for me when dividing by 1000.

It only added 18 days.

[HEATFreight]

@cotton_gin1 I feel like I've landed on this comment of yours more than once, so thanks and thanks again!