Using Python expressions to manage dates

This guide explains how to manage date and time data in a data extract.

Introduction

Use Python expressions in custom scripts such as addfieldx and convertx to manage dates in your data extract.

Choose the transformation you want to use depending on whether you want to add to or overwrite your existing data extract.

  • Use the addfieldx transformation to keep the original dates and add the converted dates to a new column in the data extract.

  • Use the convertx transformation to overwrite the original dates with the converted dates.

Rules for Python expressions

When using Python expressions in custom scripts, you must follow certain rules - these rules are covered in more details in Using Python expressions in custom scripts.

When configuring Python expressions for these use cases, it is important to remember the following rules:

  • All column_name parameters are case-sensitive. Enter the name of the column exactly as it appears in the data extract.

  • Enter column names between two braces (also known as curly brackets). For example, if the name of a column is Dates, enter {Dates}.

  • Use Python date placeholders when entering date and time formats. For a full list of available placeholders, see the Python documentation. Some of the more common date and time placeholders are:

    • %d - A day of the month as a zero-padded decimal number. For example, the 1st of the month is 01 and the 10th of the month is 10.

    • %m - A month as a zero-padded decimal number. For example, February is 02 and November is 11.

    • %Y - A year as a four-character number, for example 1999 or 2022.

    • %y - A year as a two-character number. For example, 1999 is 99 and 2022 is 22.

    • %H - An hour as a zero-padded decimal number on a 24-hour clock. For example, 8am is 08 and 8pm is 20.

    • %M - A minute as a zero-padded decimal number, for example 09 or 45.

    • %S - A second as a zero-padded decimal number, for example 05 or 36.

    • %Z - The name of the time zone for the time data, for example UTC or GMT.

    • %A - The full name of the weekday, for example Monday.

    For example, the date 01-03-2022 (1 March 2022) is in the date format %d-%m-%Y.

  • Enter dates and times between two inverted commas. For example, enter '%d-%m-%Y' or '%H:%M:%S'.

  • The format of the date and time to convert must exactly match the format in the column containing the dates and times. This includes the characters between the date and time placeholders.

    For example, if the dates to convert are in the format DD-MM-YYYY, then the Python expression must use the date format '%d-%m-%Y'. The date format '%d-%m-%y' will fail because the year format is incorrect. The date format '%d/%m/%Y' will fail because the characters between the date placeholders are incorrect.

Examples of Python expressions to manage dates

Below are some examples of Python expressions you can use to manage dates in your data extracts.

Generating today's date

To add today's date to your data extract, enter the following Python expression into the transformation:

str(__import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'output_date_format'))

To configure the Python expression, change the following parameter:

  • output_date_format - This is the format for the generated date. Enter the date format using date placeholders.

For a detailed example, see Adding certain dates to data extracts

Generating yesterday's date

To add yesterday's date to your data extract, enter the following Python expression into the transformation:

__import__('datetime').datetime.today() - __import__('datetime').timedelta(days=1)

For a detailed example, see Adding certain dates to data extracts

Calculating relative dates based on dates in the data extract

To increase the value of the date in your data extract, enter the following Python expression into the transformation:

(__import__('datetime').datetime.strptime({date_column_name},'input_date_format') + __import__('datetime').timedelta(days=1, hours=0, minutes=0)).strftime('output_date_format')

To decrease the value of the date in your data extract, enter the following Python expression into the transformation:

(__import__('datetime').datetime.strptime({date_column_name},'input_date_format') - __import__('datetime').timedelta(days=1, hours=0, minutes=0)).strftime('output_date_format')

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the dates.

  • input_date_format - This is the format of the input date. Enter the date format using date placeholders.

  • output_date_format - This is the output format for the date. Enter the date format using date placeholders.

  • days, hours, minutes - This is the value by which you want to increase or decrease the date.

Calculating the current date

To calculate the current date, month or year, enter the following Python expressions into the transformation:

  • Current date

    __import__('datetime').datetime.today().day

  • Current month

    __import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'%m')

  • Current year

    __import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'%Y')

Changing the date format

To change the date format of a column, enter the following Python expression into the transformation:

__import__('datetime').datetime.strptime({date_column_name}, 'input_date_format').strftime('output_date_format')

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the dates.

  • input_date_format - This is the current format of the dates you want to convert. Enter the current date format using date placeholders.

  • output_date_format - This is the new format to which you want to convert the dates. Enter the new date format using date placeholders.

For detailed examples, see Converting dates from one format to another

Converting a UNIX timestamp to a date

To convert a UNIX timestamp to a date format in your local timezone, enter the following Python expression into the transformation:

__import__('datetime').datetime.fromtimestamp(int({date_column_name})).strftime('output_date_format')

To convert a UNIX timestamp to a date format in UTC timezone, enter the following Python expression into the transformation:

__import__('datetime').datetime.utcfromtimestamp({date_column_name}/1000.0).strftime('output_date_format')

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the UNIX timestamp.

  • output_date_format - This is the output format for the date. Enter the date format using date placeholders.

Converting between time zones

To convert time data from one time zone to another, enter the following Python expression into the transformation:

__import__('pytz').timezone('current_timezone').localize(__import__('datetime').datetime.strptime({time_column_name},'input_time_format')).astimezone(__import__('pytz').timezone('new_timezone')).strftime('output_time_format')

To configure the Python expression, change the following parameters:

  • current_timezone - This is the current time zone of your time data. For a full list of time zones, see this reference.

  • new_timezone - This is the new time zone to which you want to convert your time data. For a full list of time zones, see this reference.

  • time_column_name - This is the name of the column that contains the time data to convert.

  • input_time_format - This is the current format of the times to convert (time data often includes a date). Enter the time and date format to convert using time and date placeholders. For example, the time and date 01-03-2022 - 22:00 (1 March 2022, 10 pm) is in the time and date format %d-%m-%Y - %H:%M.

  • output_time_format - This is the format for the converted time data. Enter the new date format using date placeholders.

For detailed examples, see Converting time data between time zones

Comparing two date columns

To compare two columns with date values, enter the following Python expression into the transformation:

__import__('datetime').datetime.strptime({date_column_1},'date_format_1') > __import__('datetime').datetime.strptime({date_column_2},'date_format_2')

This expression will return the value "True" if the Python expression is true, or "False" if the Python expression is false. The following comparison operators are available:

  • > will return "True" if the date in date_column_1 is later than the date in date_column_2

  • < will return "True" if the date in date_column_1 is earlier than the date in date_column_2

  • == will return "True" if the date in date_column_1 is the same as the date in date_column_2

  • != will return "True" if the date in date_column_1 is different from the date in date_column_2

To configure the Python expression, change the following parameters:

  • date_column_1 - This is the name of the column that contains the first date for the comparison.
    Alternatively, you can enter a specific date as a string, for example by replacing {date_column_1} with '2022-03-01'to compare the second date with 1st March 2022.

  • date_format_1 - This is the format of the first date. Enter the date format using date placeholders.

  • date_column_2 - This is the name of the column that contains the second date for the comparison. Alternatively, you can enter a specific date as a string as shown above for date_column_1.

  • date_format_2 - This is the format of the second date. Enter the date format using date placeholders.

Calculating the number of days between two dates

To calculate the number of days between two dates, enter the following Python expression into the transformation:

(__import__('datetime').datetime.strptime({date_column_1},'date_format_1') - __import__('datetime').datetime.strptime({date_column_2},'date_format_2')).days

If the date in date_column_1 is later than the date in date_column_2, this transformation will give the number of days between the two dates as a positive number. However, if the date in date_column_1 is earlier than the date in date_column_2, this transformation will give a negative number. The number is still correct.

To configure the Python expression, change the following parameters:

  • date_column_1 - This is the name of the column that contains the first date for the calculation. Alternatively, you can enter a specific date as a string, for example by replacing {date_column_1} with "2022-03-01".

  • date_format_1 - This is the format of the first date. Enter the date format using date placeholders.

  • date_column_2 - This is the name of the column that contains the second date for the calculation. Alternatively, you can enter a specific date as a string as shown above fordate_column_1.

  • date_format_2 - This is the format of the second date. Enter the date format using date placeholders.

Calculating the weekday based on the date

To calculate the weekday based on the date, enter the following Python expression into the transformation:

__import__('datetime'). datetime.strptime({date_column_name}, 'input_date_format').weekday()

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the dates.

  • input_date_format - This is the format of the input date. Enter the date format using date placeholders.

To add the weekday based on the date and combine it with the date, enter the following Python expression into the transformation:

__import__('datetime').datetime.strptime({date_column_name}, 'input_date_format').strftime('output_date_format')

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the dates.

  • input_date_format - This is the format of the input date. Enter the date format using date placeholders.

  • output_date_format - This is the output format for the date. Enter the date format using date placeholders and use %A for the weekday, e.g. %Y-%m-%d - %A.

Calculating the week number based on the date

To calculate the week number from the date column, enter the following Python expression into the transformation:

__import__('datetime').datetime.strftime(__import__('datetime').datetime.strptime({date_column_name},'input_date_format'), '%W')

To configure the Python expression, change the following parameters:

  • date_column_name - This is the name of the column that contains the dates.

  • input_date_format - This is the format of the input date. Enter the date format using date placeholders.

  • %W - This is the placeholder for the week number where Monday is the first day of the week. The week preceding the first Monday of the year will be counted as '00'.

  • %V - This is the placeholder for the ISO 8601 week number of the current year (01 to 53), where week 1 is the first week that has at least 4 days in the current year, and with Monday as the first day of the week.

Calculating the date based on the week number, year and weekday

To calculate the date based on the week number and year coming from the data extract and the defined weekday, enter the following Python expression into the transformation:

__import__('datetime').datetime.strptime(str({year_column_name}) + str({week_column_name}) + '-1', 'input_date_format').strftime('output_date_format')

Change the following parameters to configure the Python expression:

  • year_column_name - This is the name of the column that contains the years.

  • week_column_name - This is the name of the column that contains the week numbers.

  • input_date_format - This is the format of the input date parameters. Enter the date format using date placeholders.
    For example, if the year is in the format YYYY, the input_date_format should be %Y%W-%w, where %W is the week number and %w is the day of the week (as a single-digit number from 0 = Sunday to 6 = Saturday). The example expression uses 1, which represents Monday.

  • output_date_format - This is the format of the output date . Enter the date format using date placeholders.

What's next?

For some detailed examples of how you can use Python expressions in custom scripts to manage dates and times in your data extract, see the following articles: