time and date with time zone

Answered

PSA Admin

• October 01, 2020 23:35

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

• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • October 01, 2020 23:41

  

  

  

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

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • October 02, 2020 22:34
  • Edited

  

  

  

  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

  0
• 

  PSA Admin

  • October 02, 2020 22:21

  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

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • October 02, 2020 22:35

  

  

  

  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!

  0
• 

  PSA Admin

  • October 09, 2020 12:19

  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

   

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • October 09, 2020 18:13

  

  

  

  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!
  
  

  0
• 

  Abhinav Kaushal

  • February 17, 2021 23:52

  Hi,

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

  0
• 

  James K

  • February 18, 2021 15:51

  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)

  0
• 

  Abhinav Kaushal

  • February 18, 2021 15:56

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

   

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • February 19, 2021 17:36

  

  

  

  Hi James K and Abhinav Kaushal,

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

  0

Please sign in to leave a comment.