pandas date column to unix timestamp accounting for timezone and varying datetime formats

Question

I have multiple data frames with a datetime column as a string. Datetime formats vary across columns in the dataframe or across dataframes. I want to get a unix timestamp that gets interpreted by an ArcGIS application into the local timezone.

For example, one such dataframe is the following:

import pandas as pd
time_dict = {"datetime": ["2023-08-15T15:32:47.687+00:00", ""]}
test_df = pd.DataFrame(time_dict)

I tried a number of simple functions. When I submitted the unix timestamp to the ArcGIS application, it gave a date-time that was 4 hours earlier than the local time. When I tried to make some corrections for the timezone, I encountered a TypeError saying that the timezone was already present in the date.

I will provide a solution below, but maybe someone has a better one.

Answer 1

Here is a solution that I found to work well for the problem mentioned above:

import pandas as pd
from datetime import datetime
import tzlocal

def unix_datetime(df, col):
    """
    Convert string datetime to unix datetime format for dataframe column
    It reformats the date to remove existing time zone information that raises a TypeError
    It properly accounts for the timezone.
    df is name of dataframe
    col is the column name as a string
    """
    time_zone = tzlocal.get_localzone_name()
    df[col] = pd.to_datetime(df[col]).dt.strftime("%Y-%m-%d %I:%M:%S %p")
    df[col] = pd.to_datetime(df[col], errors="coerce")
    df[col] = df[col].dt.tz_localize(time_zone).dt.tz_convert(time_zone)
    df[col] = df[col].apply(lambda x: int(x.timestamp() * 1000) if pd.notnull(x) else x)
    
time_dict = {"datetime": ["2023-08-15T15:32:47.687+00:00", ""]}

test_df = pd.DataFrame(time_dict)

test_df = test_df.fillna("").copy()

unix_datetime(test_df, "datetime")

Answer 2

A colleague suggested that we just change the data type for dates in the ArcGIS application to String!