Skip to main content
Solved

Question about SQL filtering datetime with time

  • August 18, 2023
  • 4 replies
  • 11 views
J
Level 7
Forum|alt.badge.img+4

Hi expert,

 

I'm not quite good at SQL and I have a question about SQL. In my case, I would like to filter activity table with event time with its only time(hh:mm:ss). Does anybody let me know how to do that in Celonis data integration task?

Best answer by guillermo.gost12

Thanks for your quick responce and my case is latter one, I created activity table but there is some of 00:00:00 as event time(date time format.). So I would like to find out those no-time activity and update those time to correct order in process explorer.

Two ways:

 

  • Select when hour, minute and second are 0:
    • select eventtime, * from "_CEL_CEE_ACTIVITIES" where HOUR(EVENTTIME)=0 AND MINUTE(EVENTTIME)=0 AND SECOND(EVENTTIME)=0
  • Select those where the delta between the eventtime and the eventime truncated to only the date is 0:
    • select EVENTTIME,TRUNC("EVENTTIME"), *  from "_CEL_CEE_ACTIVITIES" where DATEDIFF(mcs,"EVENTTIME",TRUNC("EVENTTIME")) = 0

 

HTH

 

BTW is one of my standard test. Of course I do it over the extracted tables. I do check no time part, if there are dates in the future, and, of course, nulls and empty values.

    4 replies

    G
    Level 12

    Hmmm.... just to clarify... you have a table with a column defined as int or string, and you want to show only those records that could be converted to TIME format... am I right?

     

    BTW not activity as eventtime is DATE, so all records already fulfill the condition

     

    Or maybe you want to find which records have not a TIME part? i.e. you have created your activity table, but several activities have only date, with the time always to 00:00:00 and then it makes a mess in your Process Explorer (specially if you haven't set your sorting right)

    J
    Level 7
    Forum|alt.badge.img+4
    • jumbo.szk
    • Author
    • Level 7
    • August 18, 2023

    Thanks for your quick responce and my case is latter one, I created activity table but there is some of 00:00:00 as event time(date time format.). So I would like to find out those no-time activity and update those time to correct order in process explorer.

    G
    Level 12

    Thanks for your quick responce and my case is latter one, I created activity table but there is some of 00:00:00 as event time(date time format.). So I would like to find out those no-time activity and update those time to correct order in process explorer.

    Two ways:

     

    • Select when hour, minute and second are 0:
      • select eventtime, * from "_CEL_CEE_ACTIVITIES" where HOUR(EVENTTIME)=0 AND MINUTE(EVENTTIME)=0 AND SECOND(EVENTTIME)=0
    • Select those where the delta between the eventtime and the eventime truncated to only the date is 0:
      • select EVENTTIME,TRUNC("EVENTTIME"), *  from "_CEL_CEE_ACTIVITIES" where DATEDIFF(mcs,"EVENTTIME",TRUNC("EVENTTIME")) = 0

     

    HTH

     

    BTW is one of my standard test. Of course I do it over the extracted tables. I do check no time part, if there are dates in the future, and, of course, nulls and empty values.

    J
    Level 7
    Forum|alt.badge.img+4
    • jumbo.szk
    • Author
    • Level 7
    • August 18, 2023

    Two ways:

     

    • Select when hour, minute and second are 0:
      • select eventtime, * from "_CEL_CEE_ACTIVITIES" where HOUR(EVENTTIME)=0 AND MINUTE(EVENTTIME)=0 AND SECOND(EVENTTIME)=0
    • Select those where the delta between the eventtime and the eventime truncated to only the date is 0:
      • select EVENTTIME,TRUNC("EVENTTIME"), *  from "_CEL_CEE_ACTIVITIES" where DATEDIFF(mcs,"EVENTTIME",TRUNC("EVENTTIME")) = 0

     

    HTH

     

    BTW is one of my standard test. Of course I do it over the extracted tables. I do check no time part, if there are dates in the future, and, of course, nulls and empty values.

    Thanks for your help, its work!!

    Community Code of ConductAccessibility statement