Convert DATETIME (UTC) to specific timezone
I have an interesting quandry. I have a data flow (using MagicETL - should I be using something else?) that I am trying to build and convert a date that my MySQL data connector is bringing over.
The datetime column comes from an orders table and is called posted_date and it brings the data over as UTC from the remote server. There is a metadata table that relates to this table that outlines what each location's timezone ID is (i.e: "America/Chicago, America/New_York", etc). Shop and the Orders table relate to each other by a location_ID.
In my ETL, as you would imagine, I am bringing in both input datasets orders and shop and selecting the columns I need, for the sake of example, let us select location ID, posted_date from the orders table and location ID and timezone_ID from the shop table. My next goal is to try to convert each row on the orders table from UTC to the rows given timezone.
ex: schema and rows
In the data flow, I tried using formula object and in it used convert_TZ(
posted_date, "utc", `timezone_ID`) and when I run the preview I get an error. Any thoughts on why this would be erroring out? If not, and if you have solved this before in a DF, how did you do this?
I know you could easily convert this in the SQL queries in the data connector but the join is expensive on the DB side and I am trying to avoid doing that and let Domo ETL do this.
I appreciate your thoughts, comments, suggestions in advance, experts!