datetime.timestamp returns different values in pandas apply and dataframe selection

Question

Question

See code below demonstrating the issue. A simple pandas dataframe is created with one row and one column containing one datetime instance. As you can see, calling timestamp() on the datetime object returns 1581894000.0. Selecting the datetime object through the dataframe and calling timestamp() gives 1581897600.0. When using pandas apply function to call datetime.timestamp on each row of column 'date', the return value becomes 1581894000.0. I would expect to get the same timestamp value in all situations.

In[19]: d = datetime(2020, 2, 17)
In[20]: d.timestamp()
Out[20]: 1581894000.0 <----------------------------------+
In[21]: df = pd.DataFrame({'date': [d]})                 |
In[22]: df                                               |
Out[22]:                                                 |
        date                                             |
0 2020-02-17                                             |
In[23]: df['date'][0]                                    |
Out[23]: Timestamp('2020-02-17 00:00:00')                |
In[24]: df['date'][0].timestamp()                        |
Out[24]: 1581897600.0 <---------------------- These should be the same
In[25]: df['date'].apply(datetime.timestamp)             |
Out[25]:                                                 | 
0    1.581894e+09                                        |
Name: date, dtype: float64                               |
In[26]: df['date'].apply(datetime.timestamp)[0]          |
Out[26]: 1581894000.0 <----------------------------------+

Edit

Thanks to input from @ALollz, using to_datetime and Timestamp from pandas, as shown below seems to fix the problem.

In[15]: d = pd.to_datetime(datetime(2020,2,17))
In[16]: d.timestamp()
Out[16]: 1581897600.0
In[17]: df = pd.DataFrame({'date': [d]}) 
In[18]: df
Out[18]: 
        date
0 2020-02-17
In[19]: df['date'][0]
Out[19]: Timestamp('2020-02-17 00:00:00')
In[20]: df['date'][0].timestamp()
Out[20]: 1581897600.0
In[21]: df['date'].apply(pd.Timestamp.timestamp)
Out[21]: 
0    1.581898e+09
Name: date, dtype: float64
In[22]: df['date'].apply(pd.Timestamp.timestamp)[0]
Out[22]: 1581897600.0

Answer 1

The problem is timezone awareness. pandas doesn't always play well with the datetime module and some decisions diverge from the standard library, in this case how to deal with timezone unaware datetime objects.

This specific issue seems to have been a design choice based upon this open issue

Yah, for tz-naive we implement timestamp as if it were UTC. Among other things, this ensures that we get the same behavior regardless of where the code is running.

So to get a consistent answer you'd need a UTC localized timezone so that datetime.timestamp used that instead of your machine's local timezone.

---

from datetime import datetime
import pytz

my_date = datetime(2020, 2, 17)
my_date_aware = pytz.utc.localize(my_date)

# UTC aware is the same as pandas
datetime.timestamp(my_date_aware) - pd.to_datetime(my_date).timestamp()
#0

datetime.timestamp(my_date) - pd.to_datetime(my_date).timestamp()
#18000.0