python - pandas datetime to unix timestamp seconds

Question

From the official documentation of pandas.to_datetime we can say,

unit : string, default ‘ns’

unit of the arg (D,s,ms,us,ns) denote the unit, which is an integer or float number. This will be based off the origin. Example, with unit=’ms’ and origin=’unix’ (the default), this would calculate the number of milliseconds to the unix epoch start.

So when I try like this way,

import pandas as pd
df = pd.DataFrame({'time': [pd.to_datetime('2019-01-15 13:25:43')]})
df_unix_sec = pd.to_datetime(df['time'],unit='ms',origin='unix')
print(df)
print(df_unix_sec)

                 time
0   2019-01-15 13:25:43
0   2019-01-15 13:25:43
Name: time, dtype: datetime64[ns]

Output is not changing for the later one. Every time it is showing the datetime value not number of milliseconds to the unix epoch start for the 2nd one. Why is that? Am I missing something?

Answer 1

I think you misunderstood what the argument is for. The purpose of origin='unix' is to convert an integer timestamp to datetime, not the other way.

pd.to_datetime(1.547559e+09, unit='s', origin='unix') 
# Timestamp('2019-01-15 13:30:00')

Conversely, you can get the timestamp by converting to integer (to get nanoseconds) and divide by 10⁹.

pd.to_datetime(['2019-01-15 13:30:00']).astype(int) / 10**9
# Float64Index([1547559000.0], dtype='float64')

---

Update

Pandas docs recommend using the following method:

# create test data
dates = pd.to_datetime(['2019-01-15 13:30:00'])

# calculate unix datetime
(dates - pd.Timestamp("1970-01-01")) // pd.Timedelta('1s')

[out]:
Int64Index([1547559000], dtype='int64')

Not as fast as the method shown above, but this makes no assumption about how pandas internally stores its datetime objects.

Answer 2

You can use timestamp() method which returns POSIX timestamp as float:

pd.Timestamp('2021-04-01').timestamp()

[Out]:
1617235200.0

pd.Timestamp('2021-04-01 00:02:35.234').timestamp()

[Out]:
1617235355.234

Answer 3

value attribute of the pandas Timestamp holds the unix epoch. This value is in nanoseconds. So you can convert to ms or us by diving by 1e3 or 1e6. Check the code below.

import pandas as pd
date_1 = pd.to_datetime('2020-07-18 18:50:00')
print(date_1.value) 

Answer 4

In case you are accessing a particular datetime64 object from the dataframe, chances are that pandas will return a Timestamp object which is essentially how pandas stores datetime64 objects.

You can use pd.Timestamp.to_datetime64() method of the pd.Timestamp object to convert it to numpy.datetime64 object with ns precision.