Answered
time and date with time zone
Hello world,
I am trying to get a Mountain Time time stamp in a simple map to a date/time field in NetSuite. I have seen the nice list of formats, but that doesn't include time zones. And a nice list of time zones supported, but not how to use them. Somewhere I picked up the following syntax:
{{dateFormat "MM/DD/YYYY HH:mm:ss" (timestamp America/Denver)}}
Oddly enough, this gives me the Pacific time zone. (maybe a daylight savings issue?) However, when I try it in the developer's playground, I get a UTC time stamp. I've tried this format as well which parses but also gives a UTC time.
{{dateFormat "MM/DD/YYYY HH:mm:ss" timestamp ("America/Denver"}}
Thanks for any input.
Jim
1
Comments
Hi PSA Admin Jim!
We're looking into this for you and will get back to you as soon as possible.
Hi PSA Admin,
Can you try this:
{{dateAdd (dateFormat "YYYY-MM-DDThh:mm:ssZ") "0" "US/Mountain"}}The "0" represents an offset and then the time zone. You can use
US/Pacific
US/Mountain
US/Central
US/Eastern
Thank you so much, Courtney. This works in the playground environment (if you add the second "}" to the end), but in the live integration app preview mode, it not only reverts to UTC, but also subtracts one day from the date.
So it is 16:30 on 10/2 but the function output is: 10/01/2020 22:18:30
Thanks again. I may do some trial and error with this if I find some time.
Jim
Hi PSA Admin,
Good catch! I just updated the one I sent. I'll see if I can find anything on our side as well. Have a good weekend!
So I finally determined the problem is with NetSuite importing the date. Although the dateadd() function works as intended within the confines of the Celigo UI, NetSuite is assuming the date/time is UTC, and so is converting it to local (Mountain in this case) time. It was hard to catch because Mountain time is currently UTC -6, so if using a 12-hour clock, you get the same hour going from UTC to Mountain as the other way around. At any rate, it seems I just need to ensure that I'm sending a UTC date/time to NetSuite.
Jim
Hi PSA Admin!
Thank you so much for letting us know that you were able to get this working and for sharing how you were able to solve it!
Hi,
Do we know the solution to this problem as I am facing exactly the same issue
Hello Abhinav,
I ended up using a pre-map java function (hook) to reliably get a date in UTC, which NetSuite then converts on its own to it's local time zone.
function main(options) {
var theDate = new Date();
options.data.forEach(function (rec) {
rec.Time_Stamp = theDate;
})
return options.data.map((d) => {
return {
data: d
}
})
}
The result adds the following to the data record:
"Time_Stamp": "2021-02-18T15:45:07.783Z"
Ciao,
Jim Kelleher (PSA Admin)
Thanks Jim for sharing the details, appreciate it. I am currently using a work around where I am adding 7hrs in Celigo wrt to US/MountainTime and then when request is sent to NetSuite Connector, Celigo (bug) will subtract those 7hrs and tag it has UTC TZ. It worked that way for me.
e.g. {{dateAdd billDate "25200000"}}
Hi James K and Abhinav Kaushal,
That's great to hear! Thank you so much for sharing what has worked for you!
Please sign in to leave a comment.