How can I transform a Time column?

dmurgit1
dmurgit1 Contributor
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 Coach
    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.

    **Check out my Domo Tips & Tricks Videos

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.

    **Check out my Domo Tips & Tricks Videos

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

    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 Contributor

    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