Datetime Methods¶

In this tutorial, you will learn:

• How to use datetime methods to transform and filter datetime columns

Prerequisites:¶

To follow this tutorial, you need to have a Celonis EMS team set up and be familiar with the basic functionalities of PyCelonis.

Tutorial¶

1. Connect to your data model¶

To get started with SaolaPy, we first need to connect to our data model:

In [8]:

from pycelonis import get_celonis
import pycelonis.pql as pql

c = get_celonis()
data_pool = c.data_integration.get_data_pools().find("<YOUR_DATA_POOL>")
data_model = data_pool.get_data_models().find("<YOUR_DATA_MODEL>")

from pycelonis import get_celonis import pycelonis.pql as pql c = get_celonis() data_pool = c.data_integration.get_data_pools().find("") data_model = data_pool.get_data_models().find("")

To avoid always having to specify the data model we set it as our global default data model:

In [2]:

from pycelonis.config import Config

Config.DEFAULT_DATA_MODEL = data_model

from pycelonis.config import Config Config.DEFAULT_DATA_MODEL = data_model

For convenience, we extract the columns of tables we want to use:

In [3]:

ekpo_columns = data_model.get_tables().find("AP_EKPO").get_columns()

ekpo_columns = data_model.get_tables().find("AP_EKPO").get_columns()

2. Datetime Function Basics¶

Let's first create a series to work with that contains creation dates:

In [9]:

date_series = pql.Series(ekpo_columns.find("AEDAT"))
date_series.head()

date_series = pql.Series(ekpo_columns.find("AEDAT")) date_series.head()

Out[9]:

Index
0   2022-09-08 06:41:15.722
1   2022-09-08 06:41:15.722
2   2022-09-08 06:41:15.722
3   2022-09-08 06:41:15.722
4   2022-09-08 06:41:15.722
Name: _CELONIS_CHANGE_DATE, dtype: datetime64[ns]

To access any string method, we have to call series.dt.<method_name>. For example to get the year of a date use:

In [5]:

result = date_series.dt.year
result.head()

result = date_series.dt.year result.head()

Out[5]:

Index
0    2022
1    2022
2    2022
3    2022
4    2022
Name: _CELONIS_CHANGE_DATE, dtype: int64

Other accessor functions include:

In [6]:

# Year
date_series.dt.year

# Quarter
date_series.dt.quarter

# Month
date_series.dt.month

# Week of year
date_series.dt.week_of_year

# Day of week
date_series.dt.day_of_week

# Day
date_series.dt.day

# Hour
date_series.dt.hour

# Minute
date_series.dt.minute

# Second
date_series.dt.second;

# Year date_series.dt.year # Quarter date_series.dt.quarter # Month date_series.dt.month # Week of year date_series.dt.week_of_year # Day of week date_series.dt.day_of_week # Day date_series.dt.day # Hour date_series.dt.hour # Minute date_series.dt.minute # Second date_series.dt.second;

Lastly, it is possible to round dates to a certain frequency. Support frequencies are:

• Y, A: year
• Q: quarter
• M: month
• W: week
• D: day
• H: hour
• T, min: minute
• S: second

In [7]:

result = date_series.dt.round("D")
result.head()

result = date_series.dt.round("D") result.head()

Out[7]:

Index
0   2022-09-08
1   2022-09-08
2   2022-09-08
3   2022-09-08
4   2022-09-08
Name: _CELONIS_CHANGE_DATE, dtype: datetime64[ns]

Conclusion¶

Congratulations! You have learned how to apply datetime methods to transform date columns.