Python is powerful, concise, and robust. Simply great. Except…when you work with time. Coping with mysterious errors in transforming dates and timestamps took me hours and days of frustration. I was like, ‘why is Python doing it to me’?

I found that much of online Data Science material on time and time series analysis in Python silently gloss over the amount of preparatory work needed before you even start.

Because I work a lot with time series in Sopra Steria analytics team, I had no choice but to comprehend all this in detail. I created the summary below, so others have it easier. Enjoy.

In general, I think the following 9 areas are problematic for working with time in Python:

  1. Understanding timestamp-like data types (this article)
  2. Parsing timestamps from text
  3. Converting between timestamps (this article)
  4. Collections of timestamps (lists, ndarrays and DataFrames)
  5. Time deltas
  6. Histograms of timestamps
  7. Fixing histogram bins and edges
  8. Histograms of time deltas
  9. Time deltas in logarithmic scale

I will cover two of the subjects below. The remainign ones, time permitting, will be covered in the follow-up articles. The inspiration of the article title stems from this stackoverflow thread. Andy Hayden asked how to convert from datetime to Timestamp. Wes McKinney (the author of pandas library) starts his response with Welcome to Hell. I liked it.

What is a timestamp

A timestamp is a sequence of characters that tells you when something occurred (Wikipedia). In other words, a timestamp uniquely identifies a certain moment in time. Irritatingly, this most basic term is the source of confusion in the Python world. In the Python literature, the term has at least four meanings:

  • the generic timestamp meaning, as above
  • Python native timestamp
  • Unix timestamp
  • Pandas timestamp

In this article, we will use the Wikipedia definition. This will save us from constant confusion. Walk with me through the first circle of hell to reach the enlightenment. No kidding, it will be fun. Note: I am using numpy version ‘1.19.2’ and pandas version ‘1.2.3’.

The first circle of hell: five ways of storing a timestamp

Below are some Python time-related data types which you may encounter. Yes, it looks scary. In this section I will explain how they relate.

dtype: datetime64[ns]
dtype: datetime64[h]
dtype: datetime64[D]

1. string

Timestamps are often stored as strings. This is simple, but not very useful for data transformation.

stamp = '2021-04-13 15:33'
variable content: 2021-04-13 15:33
variable type: <class 'str'>

2. datetime.datetime

Python’s native data format is datetime.datetime. I think it is getting less popular these days. Among pandas Data Science aficionados, it is now rarely used.

from datetime import datetime
stamp_datetime = datetime(year=2021, month=4, day=13)
variable content: 2021-04-13 00:00:00 
variable type: <class 'datetime.datetime'>

3. numpy.datetime64

Numpy package introduced improved numpy.datetime64, which is like the datetime on steroids. Example:

import numpy as np
stamp_np = np.datetime64('2021-04-13 15:33')
variable content: 2021-04-13T15:33
variable type: <class 'numpy.datetime64'>

The important point about datetime64 is the unit of internal storage (the fundamental time unit, nicely explained here in Python Data Science Handbook by Jake VanderPlas), which may differ between variable. This is the grain of information used to store the data internally. For instance, a nanosecond or a second can be picked as the unit. Importantly, you must know for sure what unit you are using inside your datetime64 variable, because it will impact the calculations as we will soon see. It is a good practice to explicitly impose the timestamp64 with a certain unit (nanosecond, hour, day). Here is how. Then, the unit information is stored in the variable dtype.

np.datetime64('2021-04-13 15:33', 'ns')
show_np(np.datetime64('2021-04-13 15:33', 'h')
show_np(np.datetime64('2021-04-13 15:33', 'D')

Here is how those variables look internally:

When unit is nanosecond: 
variable content: 2021-04-13T15:33:00.000000000 variable type: <class 'numpy.datetime64'> variable dtype: datetime64[ns] 

When unit is hour: 
variable content: 2021-04-13T15 
variable type: <class 'numpy.datetime64'> variable dtype: datetime64[h] 

When unit is day: 
variable content: 2021-04-13 
variable type: <class 'numpy.datetime64'> variable dtype: datetime64[D] 
datetime[m] versus <M8[m] ?

At this point you understand the meaning of dtype datetime64[ns]. It is datetime64 with unit nanosecond. Similarly, you may encounter datetime64[s], datetime64[D] and so on. To complete our understanding of numpy.timedate64, try to run this in a Jupyter Notebook cell:


Surprisingly, those two statements show different results. As explained here, the both timestamp types are equivalent. The types designated as ‘<M8’ simply mean that your machine is little-endian (like most personal computers). If in doubt, you may verify that the following statement returns True:

np.dtype('<M8[s]') == np.dtype('datetime64[s]')

4. pandas timestamp

Pandas introduces yet another date type: pandas.timestamp. It uses np.timedate64 under the hood. You create pd.timestamp using pd.to_datetime():

import pandas as pd<br>stamp_pandas = pd.to_datetime('2021-04-13 15:33')
variable content: 2021-04-13 15:33:00
variable type: <class 'pandas._libs.tslibs.timestamps.Timestamp'>

Already quoted Jake VanderPlas refers to pd.Timestamp as best of both worlds, as compared to datetime.datetime and numpy.datetime64). It is true in terms of performance, which I hope to cover later. But regarding the API design, I am not so sure. In my view this data type has some design problems. It exhibits itself sometimes as pd.timestamp, sometimes as ‘pandas._libs.tslibs.timestamps.Timestamp', as seen above, and sometimes as np.timestamp64. For developers, this causes a lot of confusion at code design and debugging level.

5. Unix (POSIX) time

Finally, a timestamp can be stored as an integer. This is least readable for humans, but very efficient for the machines. As convention, that integer represents the number of seconds that elapsed from the so-called Epoch, defined as Jan 1st 1970. Such representation of a timestamp is referred to as Unix time, or POSIX time.

# let's calculate POSIX time for 1st January 2021
stamp_posix = (2021 - 1970) * 365 * 24 * 60 * 60
variable content: 1608336000
variable type: <class 'int'>

To summarize the material so far, there are five ways to represent a timestamp in Python, of which numpy.datetime64 is probably the most versatile. The reason of many problems is that in practice you cannot just pick your favourite timestamp format and stay with it. Rather, you will often need to convert between those formats.

The second circle of hell: converting the timestamps

Don’t stop reading, the fun hasn’t started yet.

In my practice, I often need to represent my timestamps as POSIX timestamps (integers). For instance, numpy.histogram() only accepts integers as an argument. So if you store your timestamp in numpy or pandas formats, you need to convert them. Here is how.

From pandas to Unix time

Here is how to convert from pandas.timestamp to Unix time:


Quite simple, and it works. Then, what’s wrong about it? In my view, the naming is wrong and confusing. You have to remember that:

  • Pandas timestamp type is called pd.timestamp
  • but pd.timestamp.timestamp() returns Unix timestamp (an integer)
  • the method pd.to_datetime()does not return datetime.datetime, as the name suggests. It returns pd.timestamp.

In my view, the naming of those three is confusing. Otherwise, they work nicely.

From numpy to Unix time

In contrast, to convert numpy.timedate64 to Unix timestamp you only need to use simple arithmetic. First, remember about the base unit I explained earlier. The first step is to check what base unit is being used under the hood:

# converting from timedate64 to posix time implies checking the unit and then simple mathematics

Allright, so we have minutes as the base unit. Our timestamp is represented as minutes from the Epoch. In fact, it is an integer already. And here is how to convert it to Unix time: simply multiply the minutes by 60.

posix_from_numpy_minutes = stamp_np.astype('uint64') *  * np.uint64(60)
variable content: 1618327980
variable type: <class 'numpy.uint64'>
variable dtype: uint64

And here’s an idea to make things easier, so in the future you don’t even need that arithmetics:

# It is better to enforce the unit as seconds upfront.
# This results in dtype that stores directly the seconds since Epoch:
stamp_in_seconds = np.datetime64(stamp, 's')
# now the conversion is straightforward

Try to run this code in a Jupyter notebook cell. The result should be same as above:


So far, so good? The warm up is over. The easy part is behind us. We will now learn to work with lists of timestamps. I’ll cover this in the next article.

And while you wait till I publish it, enjoy the diagram of Dante’s nine circles of hell, adapted to the misery of Python time series analyst’s life. We have covered two, and there’s seven circles to go. Time permitting.

Did you spot any errors in the above? Contact me or post your thoughts here or on Facebook.

Nine Circles of Hell: time in Python
Tagged on:                                 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.