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 )
      • Macc-PA's avatar
        Macc-PA
        Icon for Helper I rankHelper I
        5 years ago

        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.