Adjusting time zone of multiple date /time columns in multiple tables that have no timezone stamp

[zenisekd]

Hello,

I have multiple tables that have multiple date/time columns, which include time, that is in UTC, however, the data source doesn't include a timezone stamp. On top of that, I need to transform it to the local time zone (CET/CSET) for my calculations.

I have used create new column with DateTimeZone.ToLocal(DateTime.AddZone([public.sale_order.confirmation_date],0) for a single column, however this seems to me rather slow, since I have 20 tables, each containing at least one date/time column.

Is there some better way than to add columns and delete old columns, without the option of changing something on the source database?

[mahoneypat · Accepted solution]

Here are two ways to do it.  The first (recommended) adds the UTC timezone and then converts to DateTime.  The second subtracts a set # of hours from UTC (adjust for your timezone).  The first should address DST while the second doesn't.  To see how either works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that I needed to add a "Change with locale" step that you probably won't need (since we are in different locales).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZTNbiMhDMdfZZVzi4xtbMxz5FAp6mHV7S3Knvb91wwTAjN0LoPwx48/trndLvHydvn4AP/qCjhADggIv2IsIIXYdx//7nf/ITKC/79+P76+72dvLbjlqK6+9w75HfhaLbnEGEwzWIbL59vtgp1aV24EbXmsAJc0QxON0Agd+nImFpGBG+EKbk2ODpGTkjQudW5dOfSZSgtZobwfpm02OldNf/4+vifbEMCS84iOV78J5sIWYoxm3NDc0Tyr8Mvxc6YFWnFA6yJAQLMc2G7NXojAKmrQ2Kmz0yzbPcH9j2zKshX+LNsDsKTtsEfRuVAsDIEiZsQGlg6uK6AhD3EhWYiGATyd9BkgcJZM4kICYxJNjaydrMdEyctjK7INknERIGLxgPYmg+K6MCnDjs4dnWe0la0zzmhSGtCwClA0PLEjlWQBCLzQjW2dbTPbXb3UhAvdjEv4EJENZna1cfFLymZCsbEjPOHbCmRKlX4YrwEOtoogzaZ44lOdgcBZGHSfsD7d28qtr2H1EpJOdd0vnpfaXwGZJE2djrXf6pRZyKBktMP7fLd64fg0sveQrOhjx02PKUrZRIgqHfBuTd5zEGKinOrr8vkf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, date_order = _t, date_approve = _t, partner_id = _t, state = _t, date_planned = _t, amount_untaxed = _t, create_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"name", type text}, {"date_order", type text}, {"date_approve", type date}, {"partner_id", Int64.Type}, {"state", type text}, {"date_planned", type text}, {"amount_untaxed", Int64.Type}, {"create_date", type datetime}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date_planned", type datetime}, {"date_order", type datetime}}, "en-150"),
    AddUTCZone = Table.TransformColumns(#"Changed Type with Locale",{{"date_order", each DateTime.AddZone(_,0), type datetimezone}, {"date_planned", each DateTime.AddZone(_,0), type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(AddUTCZone,{{"date_order", type datetime}, {"date_planned", type datetime}})
in
    #"Changed Type1"


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZTNbiMhDMdfZZVzi4xtbMxz5FAp6mHV7S3Knvb91wwTAjN0LoPwx48/trndLvHydvn4AP/qCjhADggIv2IsIIXYdx//7nf/ITKC/79+P76+72dvLbjlqK6+9w75HfhaLbnEGEwzWIbL59vtgp1aV24EbXmsAJc0QxON0Agd+nImFpGBG+EKbk2ODpGTkjQudW5dOfSZSgtZobwfpm02OldNf/4+vifbEMCS84iOV78J5sIWYoxm3NDc0Tyr8Mvxc6YFWnFA6yJAQLMc2G7NXojAKmrQ2Kmz0yzbPcH9j2zKshX+LNsDsKTtsEfRuVAsDIEiZsQGlg6uK6AhD3EhWYiGATyd9BkgcJZM4kICYxJNjaydrMdEyctjK7INknERIGLxgPYmg+K6MCnDjs4dnWe0la0zzmhSGtCwClA0PLEjlWQBCLzQjW2dbTPbXb3UhAvdjEv4EJENZna1cfFLymZCsbEjPOHbCmRKlX4YrwEOtoogzaZ44lOdgcBZGHSfsD7d28qtr2H1EpJOdd0vnpfaXwGZJE2djrXf6pRZyKBktMP7fLd64fg0sveQrOhjx02PKUrZRIgqHfBuTd5zEGKinOrr8vkf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, date_order = _t, date_approve = _t, partner_id = _t, state = _t, date_planned = _t, amount_untaxed = _t, create_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"name", type text}, {"date_order", type text}, {"date_approve", type date}, {"partner_id", Int64.Type}, {"state", type text}, {"date_planned", type text}, {"amount_untaxed", Int64.Type}, {"create_date", type datetime}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date_planned", type datetime}, {"date_order", type datetime}}, "en-150"),
    Subtract5Hours = Table.TransformColumns(#"Changed Type with Locale",{{"date_planned", each _ - #duration(0,5,0,0), type datetime}, {"date_order", each _ - #duration(0,5,0,0), type datetime}})
in
    Subtract5Hours

Regards,

Pat

[mahoneypat]

You should be able to convert your existing columns in one step.  Can you share an example of your datetime values?

Regards,

Pat

[zenisekd]

Hi, here is a link for a sample file in excel. Bellow is a pic from Power Query. Would that work?