Time and date with time zone

Integrator.io
, ,
1

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

2

Hi @psaadmin Jim!
We're looking into this for you and will get back to you as soon as possible.

3

Hi @psaadmin,

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

4

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

5

Hi @psaadmin,

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!

6

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

7

Hi @psaadmin!
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!

8

Hi,

Do we know the solution to this problem as I am facing exactly the same issue

9

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)

10

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"}}

11

Hi @jamesk and @abhinavkaushal,

That's great to hear! Thank you so much for sharing what has worked for you!