Pandas to_datetime method giving incorrect year when converting Unix Timestamp

Question

When using to_datetime method of Pandas Dataframe to convert a Unix Timestamp to a Formatted DateTime in a column of data collected from a Data Logger I get the wrong year by 20 years. I have checked this post and many others but found no reason.

In a Pandas Dataframe Column I have a series of values which are Unix Timestamps. Using an online Epoch converter the Timestamps give the correct date and time, but using the to_datetime function in Pandas the year is 20 years out. The years should be 2024. Everything else is correct. Using the code:

df['WS_knot_TMx'] = pd.to_datetime(df['WS_knot_TMx'], unit='s')

The TimeStamp value is converted using the to_datetime method giving the result as shown below:

1073924220   2004-01-12 16:17:00
1073924690   2004-01-12 16:24:50
1073924790   2004-01-12 16:26:30

But the years are 20 years out. OK I can add 20 years like so:

df['WS_knot_TMx'] = df['WS_knot_TMx'].apply(lambda x: x - timedelta(days=365.25*20))

But this in a bit of a hack. Why is the conversion 20 years out?

Answer 1

According to https://www.epochconverter.com/ the output you are seeing is correct - the year is 2004. Are you sure you don't have a typo?

1705082400 would be 2024-01-12 06:00:00 - I note your data starts with 107, not 170

Answer 2

So knowing that the Epoch for Campbell Scientific is 1990-01-01, all I need to do is set the origin as follows:-

df['WS_knot_TMx'] = pd.to_datetime(df['WS_knot_TMx'], unit='s', origin=631152000)

the 631152000 is the difference between the 1970 and 1990 Epoch, I get the correct date. Or if using Pandas version 1.5 or less:

df['WS_knot_TMx'] = pd.to_datetime(df['WS_knot_TMx'], unit='s', origin = pd.Timestamp('1990-01-01'))

Many thanks to those above helping me out with this.