Not able to convert timezone using dayjs in integrator.io (Celigo)

Comments

8 comments

  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Junction Team it's currently not possible to use the timezone conversions with dayjs because the Internationalization API isn't available within the script environment. It's on the roadmap to add in, but for now the best way to convert datetimes is to use handlebar expressions when you map your data. https://docs.celigo.com/hc/en-us/articles/360039326071-Handlebars-helper-reference#dateFormat

    0
  • Junction Team

    Hi Tyler Lamparter. Thank you for your prompt response.

    We looked into the `dateFormat` handler.  I see that we can convert a UTC time to any other timezone but, my use case right now is that I have a timestamp in PST (America/Los_Angeles) and I want to convert it to UTC.

    Is this possible using such handlers without manually specifying the offset?

    I cannot use the offset addition because of Daylight-Savings (DST) which will alter the offset of Pacific time by 1 hour. Any suggestions on how I can handle DST and also achieve this?

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Junction Team couple questions while I try to figure something out here.

    • What is the source system on your flow?
    • What is the destination system on your flow?
    • What does your data look like and what is the current datetime format that is in PDT?
    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    If your source is exporting from NetSuite, then you could have NetSuite do the conversion. On your saved search, you would have a text formula like this: 

    to_char(new_time({datecreated}, 'GMT', 'EST'),'MM/DD/YY HH:MI AM')
    0
  • Dan Claypool Support manager NetSuite Integration Whiz
    Great Answer
    Celigo University Level 4: Legendary

    A possible solution using a handlebar expression would be the following:

    {{dateAdd (dateFormat "YYYY-MM-DDTHH:mm:ssZ" pstDate "MM/DD/YYYY HH:mm a" "UTC") "25200000"}}

    The above will take the value of the pstDate object, convert it to an ISO8601 Format, then add 25200000 milliseconds, which accounts for a 7 hour offset.

    Below is an example of this in action:

    Best Regards,
    Dan

    0
  • Junction Team

    Hi Dan Claypool. Thank you for your comment. 

    Currently, we are using it in a similar way, however, we don't want to add/subtract the timezone offset because it changes during daylight savings. So we were looking to see if there was a more dynamic approach to it.

    0
  • Junction Team

    Hi Tyler Lamparter thank you for the mentioned approaches.

    Source System: Netsuite.

    Target System: PostGreSQL DB

    Format: '06/12/2023 11:22 am' (MM/DD/YYYY HH:mm a').

    NOTE:

    Ultimately, we would prefer the `dayjs` library to assist us in the conversion of time-zones but for now we are planning to opt for a different solution on the target side.

    'Update tester set "updated_at = '2023-11-05T03:01:00.000Z'::timestamp at time zone 'America/Los_Angeles' where test_id = 5;
    PSQL seems to handle the dst on its end for now, so we will try this approach and see if it fits.
     
     

     

     

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Junction Team that's why I was curious what your source system was. Glad to see you ended up figuring it out! We have enhancements for dayjs on the roadmap and hopefully we can get that resolved so you can do this directly in javascript.

    0

Please sign in to leave a comment.