Convert epoch, which is midnight 01/01/0001, to DateTime in pandas

01/01/0001 seems to be out of range for datetime/timedelta type. We could do a little hack like this:

ref_date = pd.to_datetime('14/07/2020 17:01:40')
ref_stamp = 63730342900


bigframe['date'] = pd.to_timedelta(big_frame['date'] - ref_stamp, unit='s') + ref_date

Output:

0   2020-06-29 16:32:25
1   2020-06-29 16:32:25
2   2020-06-29 16:32:26
3   2020-06-29 16:32:26
4   2020-06-29 16:32:26
5   2020-06-29 16:32:27
6   2020-06-29 16:32:27
7   2020-06-29 16:32:27
Name: date, dtype: datetime64[ns]

While there is an accepted answer which works, I dare to suggest another solution, which might be more intuitive and less error-prone as it does not rely on specific reference values. This approach would be also generalizable to all situations.

Background for the solution

The time values in the question were seconds from the DateTime.MinValue .NET epoch, which is equivalent to 00:00:00.0000000 UTC, January 1, 0001. Fortunately, Python has also datetime.min, which is the earliest representable datetime and the same as the minimum .NET epoch.

>>> datetime.datetime.min
Out: datetime.datetime(1, 1, 1, 0, 0)

>>> datetime.datetime.min.strftime("%d/%m/%Y %H:%M:%S")
Out: 01/01/1 00:00:00

The solution

Now we can take the .NET epoch as a baseline using datetime.min and just add the seconds. We can also specify the desired output format.

import datetime
(datetime.datetime.min + datetime.timedelta(seconds=63730342900)).strftime("%d/%m/%Y %H:%M:%S")

Which gives us the correct

14/07/2020 17:01:40

Let's extend the solution to cover the Pandas DataFrame in the question.

import pandas as pd
import datetime
# Create the dataframe as in the question
df = pd.DataFrame([63730342900, 63729045145,
                   63729045145, 63729045146, 
                   63729045146, 63729045146, 
                   63729045147, 63729045147, 
                   63729045147], columns = ["datetime"])
# Apply the previous formula to every cell in the column using a lambda function
df["datetime"] = df["datetime"].apply(lambda seconds: (datetime.datetime.min + datetime.timedelta(seconds=seconds)).strftime("%d/%m/%Y %H:%M:%S"))

The result is a nicely formatted dataframe

    datetime
0   14/07/2020 17:01:40
1   29/06/2020 16:32:25
2   29/06/2020 16:32:25
3   29/06/2020 16:32:26
4   29/06/2020 16:32:26
5   29/06/2020 16:32:26
6   29/06/2020 16:32:27
7   29/06/2020 16:32:27
8   29/06/2020 16:32:27

Learn more

Of course, Python datetime has also the opposite value, datetime.max.

>>> datetime.datetime.max.strftime("%d/%m/%Y %H:%M:%S")
Out: 31/12/9999 23:59:59

Learning by stackoverflowing,