Parse Datetime

General information

Extracts date parts from the datetime data.

Description

Brick Locations

Bricks → Transformation Parse Datetime

Brick Parameters

  • Column to parse
    • In this parameter, you should specify the column in a datetime format from the dropdown list to parse.
  • Format
    • The current format of the chosen column.
      Available options:
    • Auto (by default)
    • dd-mm-yy
    • dd-mm-yyyy
    • mm-dd-yy
    • mm-dd-yyyy
    • ddmmyyyy
    • mmddyyyy
    • yyyy-mm-dd
    • dd/mm/yy
    • dd/mm/yyyy
    • yyyy/mm/dd
    • m/dd/yy
    • m/dd/yyyy
    • yyyy/dd/m
    • dd mmm(e.g. Aug, August) yyyy
    • mmm(e.g. Aug, August) dd, yyyy
    • m/dd/yyyy hh:mm PM (or AM)
    • m/dd/yy hh:mm PM (or AM)
    • dd-mm-yyyy hh:mm:ss.s
    • yyyy-mm-dd hh:mm:ss.s
    • Weekday, dd mmm(e.g. August) yyyy
    • Weekday, dd mmm(e.g. August) yyyy, hh:mm:ss.s
  • Date parts
    • Date parts to be extracted from each record.
      You can choose from the following date parts:
    • Year
    • Month
    • Day of the month
    • Weekday (0→Sunday — 6→Saturday)
    • Year without century
    • Hour 24h clock
    • Hour 12h clock
    • Minute
    • Second
    • Microsecond
    • Day of the year
    • Week of the year
    • Year-month-day
    • Year-month-day hour
    • Year-month-day hour:min
    • Year-month-day hour:min:sec
    • Multiple date parts can be selected by adding them with a + button. If a date part is not in the datetime column, it is filled with zeros.

Brick Inputs/Outputs

  • Inputs
    • Brick takes the dataset, which contains a datetime column
  • Outputs
    • Brick produces the dataset with extra columns for every extracted date part separately. The new columns are named ‘DateTimeColumn_DatePart’.

Example of usage

Let’s take the dataset “online_retail_II.csv” that contains a datetime column ‘InvoiceDate’.
notion image
Then connect it to a Parse Datetime Brick and try to get some date parts out of it.
notion image
In the settings, we pick a column to parse ‘InvoiceDate’, leave the ‘Auto’ format and select to extract ‘Week of the year’ and ‘Hour 24h clock’.
notion image
After configuring the settings, we run the pipeline and see the results in the Output data section on the right sidebar. Two new columns with the correct weeks of the year and 24h clock hours have been successfully added to the dataset.
notion image

Here is another example. We have a datetime column ‘date’ that consists of three dates in a
dd-mm-yyyy format.
notion image
  • If we use the Parse Datetime Brick, then select an ‘Auto’ format in this case and try to extract a year, month, and day, we get the following results:
    • notion image
      As we see, in the first two rows we got the wrong cast. Instead of our format, the date was automatically treated as mm-dd-yyyy. However, the third cast returned the correct results.
  • If we select the exact right current format dd-mm-yyyy of the date, we get all the correct date parts.
    • notion image
  • If we choose the current format as mm-dd-yyyy, then the first two dates can be cast to such format so that we get the corresponding date parts. In the third row, 20 cannot be a month so the date parts are filled with ‘nan’.
    • notion image
  • Finally, if we pick a completely wrong current format of the date, for example yyyy/mm/dd, we get the error.
undefined