Filtering results from the Select connector

[Automate123]

Hi Power Automate community,

I am currently working on a flow that is almost complete, however I need to make a few changes.

The flow itself basically calls Jira's REST API, from the JSON I use the Select connector to filter out results, and then put this data into a CSV file.

The issue I am having is related to the "Created" field. The JSON for this can be seen below:

"created": "2021-04-19T09:58:13.469+0100"
 
When I use the following expression on my Select connector:
item().fields.created

I extracted the correct value of:
2021-04-19T09:58:13.469+0100
 
However, if possible I would only like the date and time to be extracted. With the last 9 characters not needed. Also if possible to replace the T with a space to improve the format and readability. My desired outcome can be seen below:

2021-04-19 09:58:13

Is there something I could add to my expression, or modification I could make to my expression that would allow me to achieve this?

Thanks in advance!

[CFernandes]

Hey Mate @Automate123 

Please try the expression below -

formatDateTime(outputs('Compose'),'yyyy-MM-dd HH:mm:ss')

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Automate123]

Hi @CFernandes 

Thanks for a quick response. Could I ask where would I place these new connectors within my current flow?

[CFernandes]

Hey Mate,

Try to directly use it in the select action -

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Automate123]

Hi @CFernandes 

Thanks for a quick response. My current Select expression for the "Created" field is:

item().fields.created

How would I incorporate your expression with my current, as I tried using the expression you provided however am getting errors.

Thanks

[CFernandes]

@Automate123 Parse JSON does not return you the Created field directly?

As a test add a "Compose" and show me the value of the Created field.

[Automate123]

Hi @CFernandes 

Yes Parse JSON connector does return the created field.

When I add the expression you sent

formatDateTime(outputs('Compose'),'yyyy-MM-dd HH:mm:ss')

I am getting the following error.

In relation to you last comment, where would I add the Compose connector? Before the Select connector?

Is there not a way of achieving this just by modifying my current Select expression?

Thanks

[CFernandes]

Let try the other way round - 

Add the Compose action -

In the Compose action add the formula - 

formatDateTime(<Created>,'yyyy-MM-dd HH:mm:ss')

Let me know if the that works. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Automate123]

Hi @CFernandes 

I have added the Compose action as you suggested before my Select action.

I added the expression you gave into the input.

The Select expression for the Created field is set to the following:

item().fields.created

When I tested the flow, the output from the Created field was the same and had the unwanted characters.

Any ideas on how to achieve just a date and time format?

Thanks

[CFernandes]

Hey Mate, 

It shows me  -

Are you sure that you have passed in the output of the Compose action. 

[Automate123]

Hi @CFernandes 

Thank you for a quick response. I did not pass the output of the Compose action previously. 

This is currently the Compose connector. It is just before the Select connector

When I add the output of the Compose action to the created field

output I get is the following:

Could I ask you to clarify what I should add to the Created field?

My previous expression of item().fields.created?

The output of the Compose action?

Both?

When adding both the results returned can be seen above.

Any ideas on how I could resolve this?

Thanks

[CFernandes]

Hey Mate, Try passing.

formatDateTime( item().fields.created,'yyyy-MM-dd HH:mm:ss')

[Automate123]

Hey @CFernandes 

Thank you for your response. I tried what you suggested and it works!

There is only 1 issue that I found with the results. 

The results seem to be 1 hour behind the expected outcome.

So if I am expected the time to be 12:17:00 I am instead getting back 11:17:00.

Do you have any ideas as to why this is happening?

Maybe something to do with timezones?

Thanks

[CFernandes · Accepted solution]

Hey @Automate123 

Woohooo.... I am glad it works...

Yes, it is related to Timezone. For details see - 

https://support.microsoft.com/en-us/topic/converting-time-zone-in-microsoft-power-automate-8bce2441-...

I think you use  -

convertTimeZone( item().fields.created,'UTC','Eastern Standard Time','yyyy-MM-dd HH:mm:ss')

Note - You will have to select your time zone instead of Eastern Standard Time

As we have managed to resolve the original question related to Date-Time formatting, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Automate123]

Hey @CFernandes 

Thank you very much for the response and for all your help. Could I ask where would I put the expression you just sent?

Ahh I see I would need to add a new convert timezone connector?

I will try the expression you gave and see if that does the trick.

Thanks again