Hi, I want to create a table where I show my timestamp in 15 minute intervals, i.e. 9:00, 9:15, 9:30, 9:45 etc. Can anyone assist me with a script? Thank you

Hi Denise! I used the following prompt in Copilot: "write a sql query to group time column by 15 minute increments"

and got this response which worked in Celonis Vertica using my activity table and EVENTTIME column:

SELECT

  DATE_TRUNC('hour', timestamp_column) + 

  INTERVAL '15 minutes' * (FLOOR(EXTRACT(MINUTE FROM timestamp_column) / 15)) AS time_group,

  COUNT(*) AS count

FROM

  my_table

GROUP BY

  time_group

ORDER BY

  time_group;

The SQL code is converting the datetime column to the hour-wise value, then adding back the minutes of the floor of each 15-minute interval.

Thanks, so this is a transformation that I need to add to my table... thank you. I thought there might have been an easy component filter but I will write the transformation.

Glad it worked for you! You may be able to do something similar in PQL on the front end as well. Something else to consider as you create this extension of your datetime column: a calendar table. You can use a calendar table as a dimension of your datetimes to track various date-related attributes such as this 15-minute interval, holidays, campaign dates, etc. Calendar tables are often used in traditional data warehouse projects.

Hi @denise.kruge12, this should be possible to do in PQL:

• First create a timestamp rounded on hours:
  • PQL: ROUND_HOUR ( "Table1"."Date" )
  • Docs: ROUND_HOUR (celonis.com)
• Extract minutes of the timestamp:
  • PQL: MINUTES ( "Table1"."Column1" )
  • Docs: MINUTES (celonis.com)
• Count in which quarter the timestamp is, and multiply it with 15 to get minutes again
  • PQL: FLOOR(<MINUTES>/15)*15
• Add the quarters (in minutes) back to the timestamp
  • PQL: ADD_MINUTES ( "Table1"."Timestamps" , <ROUNDED_MINUES>)
  • Docs: ADD_MINUTES (celonis.com)

Now Create one PQL statement from this:

ADD_MINUTES ( ROUND_HOUR ( "Table1"."Date" ) , FLOOR(MINUTES ( "Table1"."Column1" )/15)*15)

See the results below:

This doesn't look like Vertica SQL, so I'm doubting if this works in Celonis...