DOMO Time Import Problems

TargetDataGuy
TargetDataGuy Member
in Magic ETL

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.

Best Answer

  • TargetDataGuy
    TargetDataGuy Member
    Answer ✓

    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

     

     

Answers

  • ST_Superman
    ST_Superman Domo Employee

    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

  • TargetDataGuy
    TargetDataGuy Member
    Answer ✓

    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

     

     

  • TargetDataGuy
    TargetDataGuy Member

    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.).

Categories