How to convert datetime object to milliseconds

Question

I am parsing datetime values as follows:

df['actualDateTime'] = pd.to_datetime(df['actualDateTime'])

How can I convert this datetime objects to milliseconds?

I didn't see mention of milliseconds in the doc of to_datetime.

Update (Based on feedback): This is the current version of the code that provides error TypeError: Cannot convert input to Timestamp. The column Date3 must contain milliseconds (as a numeric equivalent of a datetime object).

import pandas as pd
import time

s1 = {'Date' : ['2015-10-20T07:21:00.000','2015-10-19T07:18:00.000','2015-10-19T07:15:00.000']}

df = pd.DataFrame(s1)

df['Date2'] = pd.to_datetime(df['Date'])

t = pd.Timestamp(df['Date2'])

df['Date3'] = time.mktime(t.timetuple())

print df

Oocyte

Paste some data df['actualDateTime'] – Emmons

Accept rai's answer if it is satisfactory! – Emmons

meta.stackexchange.com/questions/43478/… – Emmons

"the doc of to_datetime, however there is no reference to milliseconds" is simply incorrect, even back in 0.17 doc it said "format: strftime to parse time, eg “%d/%m/%Y”, note that “%f” will parse all the way up to nanoseconds." And pandas strftime doc page refers you to Python datetime for more details. – Okubo

Answer 1

You can try pd.to_datetime(df['actualDateTime'], unit='ms')

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html

says this will denote in epoch, with variations 's','ms', 'ns' ...

Update

If you want in epoch timestamp of the form 14567899..

import pandas as pd
import time
t = pd.Timestamp('2015-10-19 07:22:00')
time.mktime(t.timetuple())

>> 1445219520.0

Latest update

df = pd.DataFrame(s1)
df1 = pd.to_datetime(df['Date'])
pd.DatetimeIndex(df1)
>>>DatetimeIndex(['2015-10-20 07:21:00', '2015-10-19 07:18:00',
           '2015-10-19 07:15:00'],
          dtype='datetime64[ns]', freq=None)
df1.astype(np.int64) 
>>>0    1445325660000000000
1    1445239080000000000
2    1445238900000000000
df1.astype(np.int64) // 10**9
>>>0    1445325660
1    1445239080
2    1445238900
Name: Date, dtype: int64

Commence

If I do so, I don't see any effect. The result is still 2015-10-19 10:39:00 – Oocyte

is the desired o/p milliseconds since epoch, something like 146887891 ? – Commence

Yes. But if I replicate your code like this: df['actualDateTime'] = pd.to_datetime(df['actualDateTime']) t = pd.Timestamp(df['actualDateTime']) df['actualDateTime'] = time.mktime(t.timetuple()) It says: TypeError: Cannot convert input to Timestamp – Oocyte

See my update. I posted your code with my sample data so that you can see the error. – Oocyte

import numpy; and also no need for DatetimeIndex; statements 1, 2 and 5 are enough – Commence

The last 3rd update is plainly wrong: df1.astype('u8') converts into nanoseconds, and dividing it further by 1e9 makes it seconds, not milliseconds (that would be 1e6). Anyhow, @mike-müller's reply is succinct and correct. – Alkane

Answer 2

Timestamps in pandas are always in nanoseconds.

This gives you milliseconds since the epoch (1970-01-01):

df['actualDateTime'] = df['actualDateTime'].astype(np.int64) / int(1e6)

Tampa

TypeError: cannot astype a datetimelike from [datetime64[ns]] to [int32] – Oocyte

@KlausosKlausos This is a Windows (or 32bit OS) problem. Updated to int64. – Pryer

NameError: name 'np' is not defined – Pilsen

import numpy as np – Pryer

Answer 3

This will return milliseconds from epoch

timestamp_object.timestamp() * 1000

Abeyta

pandas.pydata.org/pandas-docs/stable/reference/api/… – Scrappy

Answer 4

pandas.to_datetime is to convert string or few other datatype to pandas datetime[ns]

In your instance initial 'actualDateTime' is not having milliseconds.So, if you are parsing a column which has milliseconds you will get data.

for example,

df
Out[60]: 
                         a  b
0  2015-11-02 18:04:32.926  0
1  2015-11-02 18:04:32.928  1
2  2015-11-02 18:04:32.927  2

df.a
Out[61]: 
0    2015-11-02 18:04:32.926
1    2015-11-02 18:04:32.928
2    2015-11-02 18:04:32.927
Name: a, dtype: object

df.a = pd.to_datetime(df.a)

df.a
Out[63]: 
0   2015-11-02 18:04:32.926
1   2015-11-02 18:04:32.928
2   2015-11-02 18:04:32.927
Name: a, dtype: datetime64[ns]

df.a.dt.nanosecond
Out[64]: 
0    0
1    0
2    0
dtype: int64

df.a.dt.microsecond
Out[65]: 
0    926000
1    928000
2    927000
dtype: int64

Emmons

Yes I don't have milliseconds. However I thought it's possible to make such conversion. Well, to get seconds, should I use df.a.dt.second? I've tried it, but it does not seem to be working. – Oocyte

you mean you want milliseconds not microseconds ? – Emmons

Right, I need milliseconds. Also, I have 2015-11-02 18:04, not 015-11-02 18:04:32.926 – Oocyte

If that solved your problem accept @Emmons 's answer. – Ptisan

It did not solve the problem. If I run df['actualDateTime'].dt.microsecond, I get 0 in all records. – Oocyte

So, what you are expecting to come? – Emmons

I expect to get something similar to what got in the last lines of your example, i.e. 926000. So, the only thing I need is to get a single timestamp instead of a datetime. I will later use these timestaps for Clustering analysis, where I need all variables to be numeric. – Oocyte

so you mean conversion of (year,month,day,hr,min,sec) to milliseconds? – Emmons

Exactly. This is what I mean. The solution of @rai seems to be correct, but it provides an error: TypeError: Cannot convert input to Timestamp – Oocyte

Answer 5

For what it's worth, to convert a single Pandas timestamp object to milliseconds, I had to do:

import time
time.mktime(<timestamp_object>.timetuple())*1000

Repulsion

Answer 6

For python >= 3.8, for e.g.

pd.DataFrame({'temp':[1,2,3]}, index = [pd.Timestamp.utcnow()]*3)

convert to milliseconds:

times = df.index.view(np.int64) // int(1e6)
print(times[0])

gives:

1666925409051

Note: to convert to seconds, similarly e.g.:

times = df.index.view(np.int64) // int(1e9)
print(times[0])
1666925409

Radioactivate

Answer 7

from datetime import datetime

print datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]

>>>> OUTPUT >>>>
2015-11-02 18:04:32.926

Ptisan

It says: strftime() argument 1 must be string or read-only buffer, not Series – Oocyte

I posted sample data. – Oocyte

I also tried thisdf['actualDateTime'] = (df['actualDateTime']-datetime(1970,1,1)).total_seconds() But it says that 'Series' object has no attribute 'total_seconds' – Oocyte

your values of datetime data are stored in df variable. – Ptisan

Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually wont help the OP to understand their problem. – Jeanajeanbaptiste