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

Question

I have a column with a series of timestamps in it. Originally I thought they are in Unix timestamps system so I used the following code to convert them to date time.

big_frame['date'] = pd.to_datetime(big_frame['filename'],unit='s')

however, it gave me odd results so I researched a bit more and found out the timestamps are basically using the .net epoch, which is midnight 01/01/0001. So the file names are the number of seconds from that epoch. How can I convert a column in my data frame to the right DateTime?

For example if I have this

63730342900 

The result should be

14/07/2020 17:01:40

Edit:

https://www.epochconverter.com/seconds-days-since-y0

This is the only site I could find that converts the above timestamp correctly

Below is the column I want to convert

0     63729045145
1     63729045145
2     63729045146
3     63729045146
4     63729045146
5     63729045147
6     63729045147
7     63729045147

Answer 1

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]