Convert a columns of datatype datetimezone to datetime

[h4tt3n]

Hello,

I have a table with timestamped data of datatype datetimezone, eg. 07-08-2020 08:00:00 + 02:00. 

How do I convert this to datetime in a way that takes tz info into account, eg. 07-08-2020 10:00:00?

Note that the + 02:00 part has been added to the timestamp, not simply stripped away.

If I try to manually convert the datatype, I get an error.

Cheers, Mike

[dufoq3]

EDIT: I've just noticed that this is almost 5 years old topic, but maybe it will help to someone...

Hi @h4tt3n, check this:

let
    Source = #table(null, {{"07-08-2020 08:00:00 +02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From([Column1]) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime

If there is a space after + sign (as you showed us in your sample) use this:

let
    Source = #table(null, {{"07-08-2020 08:00:00 + 02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From(Text.Combine(Text.Split([Column1], " + "), " +")) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime

[ajerovich]

Unsure if this is helpful to anyone. I was finding the solution getting the most kudos was substracting the timezone and this was the opposite of I wanted it. I ended up just making this stupidly long calculation instead:

if [DateTimeZone] is null then null else
DateTime.From(Text.BeforeDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))
+
#duration(0,
Time.Hour(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))),
Time.Minute(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))),
Time.Second(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))))

First part just makes sure the value isn't null.

Then it extracts the text before the delimiter "+" which in my case is the datetime, then adds the text after the "+" delimiter which is my timezone information.

Not exactly convient but does the job.

Don't forget to convert the column to a datetime once done.

[AGo]

First convert column to Date/Time/Zone and then here's my solution in PowerQuery:

Table.TransformColumns(#"PREVIOUS STEP NAME",{{"FIELD NAME", each if _=null then null else DateTime.From(_)+#duration(0,Number.From(Text.Start(Text.End(Text.From(_),6),3)),0,0), type datetime}})

[Ajinkya369]

Hi @h4tt3n ,

You can acheive this by splitting the column.Below i have attached the screenshots for your understanding.

This is your orignal column with Datetimezone

Split Column

 Split column:

Select or enter delimeter : Custom

Use +

Split at: Left-most delimeter

Click on OK

Result :

Result,which is a datetime datatype.

If your problem is solved please accept this as solution.

Thank you

[h4tt3n]

orry, this simply discards timezone info, it doesn't incorporate it into the time. However, I have found the solution with a python script.

[Macc-PA]

Hi, Can you share the steps you used to add the timestamp to the date using python

[h4tt3n]

@Macc-PA I think this is the script. After all, it's one year ago, and we ended up not using it anyway.

import pytz

import datetime

tz = pytz.timezone('Europe/Copenhagen')

dt = datetime.datetime.utcnow()

offset_seconds = tz.utcoffset(dt).seconds

offset_hours = offset_seconds / 3600.0

#print ("{:+d}:{:02d}".format(int(offset_hours), int((offset_hours % 1) * 60)))

print( offset_hours )

[Macc-PA]

Thanks for that - in the end we didnt need to use it.

The problem was solved by ensuring our odata datasource was sending the datetime as UTC, ie not converting it to local time.

Once that happened,  converting the column to DataTime applied the UTC offset as expected even for datetimes where daylight saving was involved.

[artemus]

In effect you are asking to convert a datetimezone value to a datetime in UTC:

let

    dtz = ...,

    udtz = DateTimeZone.SwitchZone(dtz, 0),

    dt = DateTimeZone.RemoveZone(udtz)

in

    dt

[Mariusz]

Hi @h4tt3n 

Convert into datetime but add another step

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNbDQNTIwMlAwsLAyMAAiBW0DIyClFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[h4tt3n]

I have tried to add the conversion as another step, but unfortunately this doesn't work either. Challenge remains unsolved.