Datetime defaulting to 1970 in pandas

Question

I am having issues converting my date in the right format.

I have a column that looks like this: 20130525, stored as an int64.

I am trying to set it up as a date, but having issues.

I wrote a function that looks like this:

def reformat_dates(df):
    df['column'] = pd.to_datetime(df['column'], format = "%Y-%m-%d")

    return df

but when I execute the function, I end up with a column like this:

1970-01-01 00:00:00.020130525

Is there something wrong with my function that makes it default this way? I would like the format to be

2013-05-25

Solution 1

Can this be useful?

df['column'] = pd.to_datetime(df['column'], format = "%Y%m%d").dt.strftime('%Y-%m-%d') 

Solution 2

I think the column that you are converting is in UNIX timestamp format.

You should use unit='s'.

def reformat_dates(df):
    df['column'] = pd.to_datetime(df['column'], unit='s')
    return df

Solution 3

import datetime
import numpy as np
df['creationDate']  =(df['creationDate']).astype(np.int64) // 10**3
 
df['pubDate'] =   (df['pubDate']).astype(np.int64) // 10**3

this is related to the pandas issue that converts Datetime defaulting to 1970 , the following line of code worked for me

result1['pub']=pd.to_datetime(result1['pubDate'], unit='s')

Comments

[Mig82]

Hi @Shaina Raza Please clearly state what the context and question are, what have you tried so far and how it is failing. Please read through this and improve your question accordingly so that you increase your chances of getting a good answer: stackoverflow.com/help/how-to-ask

[jottbe]

Hi @Mig82: that's not a question, but an answer.

[jottbe]

@ShainaRaza: could you maybe just add some short description of what your code does? Sure, you divide by 1000, but why?

[Mig82]

Yep, you're right @jottbe, my bad. I made the comment from the review queue and got a bit confused, obviously. Sorry about that.

[Charles Naccio]

This was exactly my issue. Thank you! I noticed I was able to convert my timestamp via one of the online converters, but kept getting a 1970s date with Pandas. I assumed all unix timestamps were in the same format. Learned something new!

[Meir Gabay]

Worked for me when I removed // 10**3