MySQL using different timezone to workplace setting

mccreathbecky
mccreathbecky Member
in Archive

I've just discovered that cards will use the workplace setting for a timezone, [e.g. AEST] whereas MySQL Dataflows will use GMT.

 

I'm trying to sum total sales by month, to then apply a monthly commission formula. 

This sort of structure:

|Month | Sales | Commission|

|2017-09|10,000|3,000|

|2017-10|12,000|4,000|

Where Month = DATE_FORMAT(`completedAt_$date`,'%Y-%m')

and Sales = SUM(`Amount`)

 

I need to use MySQL rather than beastmode calculations in a card, because ultimately I'll need to sum it down to a single number and use that figure in other calculations.

 

What I've discovered is that if I view the data used by my card and SQL on an individual transaction level, it's identical [ID, Amount] apart from the 'Month' field, which will vary on the boundary between months where, say, the transaction in AEST is in Nov, but in GMT is Oct. This creates substantial disparities in my data!!!

 

I'm unsure if this is by design or a Domo bug ~ any suggestions on how to automatically have mySQL use the workplace timezone rather than GMT?

 

Comments

  • Tomo
    Tomo Contributor

    Hi @mccreathbecky

     

    If the original data is all UTC, setting the time zone from the administrator screen will convert Domo when importing data.

    https: // [your-instance] .domo.com / admin / companysettings
    Time zone

     

    However, this function can not be used if the original data is AEST or GMT.
    In this case, you need to set the Time zone setting to UTC.
    Then, in DataFlow SQL, convert like convert_tz (`date`, 'GMT', 'AEST').

     

    ===

    元のデータが全てUTCであるならば、管理者画面からタイムゾーンを設定すると、Domoがデータ取り込み時に変換してくれます。

    https://[your-instance].domo.com/admin/companysettings
    Time zone

     

    しかし、元のデータが AEST や GMT の場合、この機能は利用できません。
    この場合は、Time zone 設定をUTCにしておく必要があります。
    そして、DataFlow SQLで、convert_tz(`date`, 'GMT', 'AEST') の様に変換します。

Categories