0

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?

Improve this question
2
  • 3
    1073924220 is in 2004. Are you putting the right number in your online converter? By comparison, 2024-01-12 16:17:00 is 1705094220, not 1073924220 Commented Jan 12, 2024 at 18:44
  • 1
    Are you sure your data logger is producing true Unix timestamps and isn't off by 20 years? For example, apparently Garmin devices use 31 Dec 1989 as their epoch date, which means their timestamps would appear 20 years early if interpreted as Unix: en.wikipedia.org/wiki/… Commented Jan 12, 2024 at 18:58

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Well thank you all for pointing out my error. When I used the Epoch Converter, I looked at the wrong line for the result. Must be tired. Now I need to find out why the Logger is showing the correct time for each entry, but is 20 years out for the Max/Min Data Times. I have even reset the Logger Time, but it is still incorrect. I will have to chase Campbell Scientific!
From a quick look here, it does seem like Campbell Scientific's "epoch" times are actually based on 1990 rather than 1970: campbellsci.in/forum?forum=1&l=thread&tid=16832
I was going to look this up myself on the forum today, you neat me to it! That explains everything. Why is there not a standard everyone uses!!
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.