DOMO Time Import Problems

Reply
Yellow Belt

DOMO Time Import Problems

I have an Excel data file with the following information:

 

Date of incident | Time of Incident

12/27/2017        |  01:00 PM

 

The literal value in the time field is "01:00 PM", verified by converting to .CSV and opening in text editor.  However, when DOMO imports the time field, it correcly assigns it as a time data type, however it imports the value incorrectly.  The above value of "01:00 PM" is converted to "2018-01-02 07:00:00".  The date portion appears to be linked to when I import the data, but I cannot understand where/why the time portion is coming across incorrectly.  Perhaps it's a time/zone conversion... Any help would be greatly appreciated.


Accepted Solutions
Highlighted
Yellow Belt

Re: DOMO Time Import Problems

Thanks @ST_-Superman-_.  I'm using DOMO Workbench to pull in data from Excel and did not see anywhere that I could use SQL statements.  But, your suggestion did point me to a Transforms section which has, ironically enough, a Shift Data Timezone Transform.  Thanks, I will give this a shot and see what the results are.ShiftTimezone.jpg

 

 


All Replies
Red Belt

Re: DOMO Time Import Problems

My guess is that the data is coded in a different timezone.  (UTC?)

 

You can adjust the timezone using this statement:

SELECT CONVERT_TIMEZONE('EST', `timestamp field`)

this should convert the timezone to EST time.

 

 

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Time-Zones/m-p/21326#M2285


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Highlighted
Yellow Belt

Re: DOMO Time Import Problems

Thanks @ST_-Superman-_.  I'm using DOMO Workbench to pull in data from Excel and did not see anywhere that I could use SQL statements.  But, your suggestion did point me to a Transforms section which has, ironically enough, a Shift Data Timezone Transform.  Thanks, I will give this a shot and see what the results are.ShiftTimezone.jpg

 

 

Yellow Belt

Re: DOMO Time Import Problems

The workbench tranformation worked perfectly, the times are now synced correctly.  One more question though.  How do I get the output to display just the time, and not the date time?  The system is automatically appending the current upload date to the field, so for today's import I have '2018-01-04 13:00:00' when I just need to see '13:00:00'.  I believe I can build a Beast Mode field to present just the time, but I'd rather do it natively if possible.

 

Also, under format I juat have Justification and Style (bold, italics, etc.).

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!