How can I transform a Time column?

dmurgit1
dmurgit1 ⚪️
in Connectors

I have an Excel file with a column of type Time. When I import the excel file via Domo's file upload connector, the values in the Time column are off by 5 hours. I don't see a transform, Shift Data Timezone Transform option as I do in Workbench. And I don't see a transform option in Magic ETL either.

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    When time elements are uploaded into Domo, it assumes they are in UTC already and then displayed for you in the timezone in your company settings, which is why you see a shift. If you can't make use of the option in Workbench for this file, you can adjust it in Magic ETL by connecting a formula tile to the input dataset and then either using the ADDTIME(), SUBTIME() or CONVERT_TZ() functions to move it to UTC.

    Hope this helps.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    As a note of clarity CONVERT_TZ will convert a time based on the current day's UTC offset and not when the date actually occurred. So if you have a date from 6 months ago affected by Daylight Savings Time (an extra hour off of UTC) and you try to convert it today it will be off by an hour from what's expected (It'll subtract 4 hours instead of 5 because right not it's 4 hours off of UTC and not 5). This is due to a limitation with the CONVERT_TZ function.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • dmurgit1
    dmurgit1 ⚪️

    Thank you Grant That answers my next question on DST. So even if I transformed the UTC field with ADDTIME(timefld,seconds), I would have to update the parameters depending on the day it is transformed? Sounds like it could get real messy. I think I'll have the excel file time column preformatted to string before ingesting into domo.

Categories