Skip to main content

Celonis Product Documentation

MILLIS_BETWEEN
Description

MILLIS_BETWEEN computes the difference between two timestamps in milliseconds. If a calendar is specified, only the passed time in the calendar is considered.

Supported input column types: DATE

Output column type: FLOAT

If a calendar is specified and either timestamp is not covered by the given calendar, MILLIS_BETWEEN returns the same result as if the next timestamp covered by the calendar was used instead.

If a calendar is specified and either the start or end timestamp are outside of the scope of the calendar, NULL is returned. For the factory calendar the scope contains the complete year containing the first defined shift (so also all days before the first shift in that year), the complete year containing the last defined shift and all years in between.

Syntax
  MILLIS_BETWEEN ( start_column, end_column [, calendar_specification [, calendar_id_column]] )
 
  • start_column: Start timestamp

  • end_column: End timestamp

  • calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT

  • calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.

NULL handling

If any parameter is NULL, the result is NULL as well.

Exceptions

The MILLIS_BETWEEN operator does not work if there are any gaps in the years covered by the respective WORKDAY_CALENDAR. An error will be returned if gaps are found in the workday calendar configuration.

Examples

[1]

A simple MILLIS_BETWEEN example without a specified a calendar.

Query

Column1

         MILLIS_BETWEEN ( "TimestampTable"."Start" , "TimestampTable"."End" )
        

Input

Output

TimestampTable

Start : date

End : date

Sat Jan 01 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.999

Mon May 09 2005 12:01:10.000

Mon May 09 2005 12:01:11.000

Result

Column1 : float

-1.0

1000.0

[2]

A simple MILLIS_BETWEEN example using a weekday calendar. The first of January 2018 is a Monday.

Query

Column1

         MILLIS_BETWEEN ( "TimestampTable"."Start" , "TimestampTable"."End" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Start : date

End : date

Mon Jan 01 2018 10:00:00.000

Mon Jan 01 2018 10:00:01.111

Tue Jan 02 2018 09:00:00.000

Mon Jan 01 2018 16:59:59.995

Result

Column1 : float

1111.0

-5.0

[3]

If either input timestamp is not covered by the given calendar, the result is the same as if the next timestamp covered by the calendar was used instead. In the first row, the start is not during a shift. In the second row, the end date is not during a shift.

Query

Column1

         MILLIS_BETWEEN ( "TimestampTable"."Start" , "TimestampTable"."End" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Start : date

End : date

Mon Jan 01 2018 08:00:00.000

Mon Jan 01 2018 09:00:00.000

Mon Jan 01 2018 16:59:59.999

Mon Jan 01 2018 18:00:00.000

Result

Column1 : float

0.0

1.0

[4]

MILLIS_BETWEEN can also be used with an intersection of different calendars. Only the timestamps that are covered by both calendars are taken into account.

Query

Column1

         MILLIS_BETWEEN ( "TimestampTable"."Start" , "TimestampTable"."End" , INTERSECT ( WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 WEDNESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 ) , FACTORY_CALENDAR ( "FactoryTable"."START" , "FactoryTable"."END" ) ) )
        

Input

Output

FactoryTable

START : date

END : date

Tue Jan 02 2018 09:00:00.000

Tue Jan 02 2018 12:00:00.000

Tue Jan 02 2018 13:00:00.000

Tue Jan 02 2018 16:00:00.000

Wed Jan 03 2018 09:00:00.000

Wed Jan 03 2018 12:00:00.000

Wed Jan 03 2018 13:00:00.000

Wed Jan 03 2018 16:00:00.000

TimestampTable

Start : date

End : date

Tue Jan 02 2018 11:59:59.900

Tue Jan 02 2018 13:00:00.000

Wed Jan 03 2018 09:00:00.010

Tue Jan 02 2018 15:59:59.990

Result

Column1 : float

100.0

-20.0

[5]

It is also possible to use different calendars for different rows by specifying an additional calendar id column.

Query

Column1

         MILLIS_BETWEEN ( "TimestampTable"."Start" , "TimestampTable"."End" , FACTORY_CALENDAR ( "FactoryTable" ) , "TimestampTable"."Ident" )
        

Input

Output

FactoryTable

IDENT : string

START : date

END : date

'DE'

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

'US'

Tue Jan 02 2018 14:00:00.000

Tue Jan 02 2018 23:00:00.000

TimestampTable

IDENT : string

Start : date

End : date

'DE'

Tue Jan 02 2018 00:00:00.000

Tue Jan 02 2018 08:00:01.001

'US'

Tue Jan 02 2018 00:00:00.000

Tue Jan 02 2018 14:00:01.002

Result

Column1 : float

1001.0

1002.0

See also: