I’ve set up a workflow to connect Google Sheets with Jira to create tickets. The “Timestamp” field in my Google Sheet is formatted as “Jan 10, 2025 4:36am UTC.” I need to convert this timestamp to UTC+7 for display in the Jira ticket using {{ $json["Timestamp"] }}.
Do you have any suggestions on how to achieve this?
It looks like your topic is missing some important information. Could you provide the following if applicable.
Anything between {{ }} is JavaScript, so use Javascript to add 7 hours to the timestamp.
{{ new Date($json[“Timestamp”]).setHours(new Date($json[“Timestamp”]).getHours() + 7) }}
Or if in milliseconds:
{{ new Date($json[“Timestamp”] * 1000).setHours(new Date($json[“Timestamp”] * 1000).getHours() + 7) / 1000 }}
Good luck!
@Michel_Hageman It doesn’t seem to be working. Please see the attached images:
The “Timestamp” value is stored in Google Sheets.
I’m using the JavaScript code you suggested in my n8n (V2 and V3 respectively).
Alright… this should do the trick I hope:
You can use a ‘date & time’-node from n8n to add 7 hours to the $json[“Timestamp”]. You don’t have to convert it first.
Then after the date&time-node, you want to insert an ‘Edit Fields’ node and drag the output of the date&time-node into the ‘drag input fields here’ and if you have other fields that join the json-data before, then add them all by using the slider. Then in the expression field of the ‘Edit Field’-node, where you dragged in ‘newDate’ from the date&time-node, you want to add this:
{{ $json.newDate.toDateTime().format(‘yyyy-MM-dd HH:mm:ss’) }}
where newDate stems from the earlier date&time-node which converted $json[“Timestamp”] into newDate (it gives this as a standard-name, but if you want you can change it back into “Timestamp” of course.)
This will transform the newDate to datetime and then you can choose to format. In this case the output would be something like:
2025-02-25 12:33:45
but the formatting option can be like you want it to be. Read the help-text from the format() command to get an insight.
I hope this will work for you! I think it’s easier working with the date&time-node.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
