How can I transform a Time column?
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
-
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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 theCONVERT_TZ
function.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 466 Domo Developer
- 50 Domo Everywhere
- 106 Apps
- 717 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 27 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 654 ひらめき共有