Forum Discussion

h4tt3n's avatar
h4tt3n
Icon for Helper V rankHelper V
6 years ago

Convert a columns of datatype datetimezone to datetime

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...
Need Help
Ajinkya369's avatar
Ajinkya369
Icon for Resolver III rankResolver III
6 years ago

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's avatar
    h4tt3n
    Icon for Helper V rankHelper V
    6 years ago

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

    • artemus's avatar
      artemus
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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

       

    • Macc-PA's avatar
      Macc-PA
      Icon for Helper I rankHelper I
      5 years ago

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

      • h4tt3n's avatar
        h4tt3n
        Icon for Helper V rankHelper V
        5 years ago

        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 )