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
Major Brown 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
New to Dojo Please update your Dojo handle and personal settings. Click here!