How do I convert timestamp to datetime format in python for cosmos data?

user_18572659 0
Viewed 186

After querying cosmos db I receive a ts column in my dataframe(df) which has datetime in the below format

df
_ts         etag
1646255207   xyz
1646257427   abc
1646297798
1646333451   dfg

How do I convert this to a datetime format. The reason i want to is because, I only want data from last 24 hours.

I tried-

from datetime import datetime
d = "1646255207"
d = int(d[:10])
datetime.fromtimestamp(d).strftime('%Y-%m-%d %I:%M:%S %p')
Out[39]: '2022-03-02 04:06:47 PM'

Is there a better way to just add a filter to _ts and get last 24 hour data from _ts itself?

azure-cosmosdb-sqlapi azure-cosmosdb pandas azure python
1 Answers

Use pd.to_datetime with unit="s":

df["Date"] = pd.to_datetime(df["_ts"], unit="s")
print(df)

Prints:

          _ts  etag                Date
0  1646255207   xyz 2022-03-02 21:06:47
1  1646257427   abc 2022-03-02 21:43:47
2  1646297798  None 2022-03-03 08:56:38
3  1646333451   dfg 2022-03-03 18:50:51

And to get last 24h data:

print(df[df["Date"] > df["Date"].max() - pd.Timedelta(hours=24)])
user_100359850
Related