Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

DATEDIFF

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

DATEDIFF computes the difference between two dates in the specified time unit.

Supported input column types: DATE

Output column type: FLOAT

Syntax
DATEDIFF ( time_unit, table.date_column1, table.date_column2 )

  • time_unit: ms | ss | mi | hh | dd | mm | yy

    • ms: Calculate the difference between the given dates in milliseconds (equals MILLIS_BETWEEN)

    • ss: Calculate the difference between the given dates in seconds (equals SECONDS_BETWEEN)

    • mi: Calculate the difference between the given dates in minutes (equals MINUTES_BETWEEN)

    • hh: Calculate the difference between the given dates in hours (equals HOURS_BETWEEN)

    • dd: Calculate the difference between the given dates in days (equals DAYS_BETWEEN)

    • mm: Calculate the difference between the given dates in months (equals MONTHS_BETWEEN)

    • yy: Calculate the difference between the given dates in years (equals YEARS_BETWEEN)

NULL handling

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

Examples

[1] Calculate the difference between dates of two rows in milliseconds (equals MILLIS_BETWEEN).

Query

Column1

DATEDIFF ( ms , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : 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] Calculate the difference between dates of two rows in seconds (equals SECONDS_BETWEEN).

Query

Column1

DATEDIFF ( ss , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Sat Jan 01 2000 00:00:59.999

Fri Dec 31 1999 23:59:59.999

Mon May 09 2005 12:01:10.000

Mon May 09 2005 12:01:11.500

Result

Column1 : FLOAT

-60.0

1.5

[3] Calculate the difference between dates of two rows in minutes (equals MINUTES_BETWEEN).

Query

Column1

DATEDIFF ( mi , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Sat Jan 01 2000 01:00:29.999

Fri Dec 31 1999 23:59:59.999

Mon May 09 2005 12:00:00.000

Mon May 09 2005 00:00:00.000

Result

Column1 : FLOAT

-60.5

-720.0

[4] Calculate the difference between dates of two rows in hours (equals HOURS_BETWEEN).

Query

Column1

DATEDIFF ( hh , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Sat Jan 01 2000 23:29:59.999

Fri Dec 31 1999 23:59:59.999

Mon May 09 2005 12:00:00.000

Mon May 09 2005 00:00:00.000

Result

Column1 : FLOAT

-23.5

-12.0

[5] Calculate the difference between dates of five rows in days (equals DAYS_BETWEEN).

Query

Column1

DATEDIFF ( dd , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Sun Jan 02 2000 00:01:02.000

Fri Dec 31 1999 23:59:59.000

Sat Jan 01 2000 12:01:00.000

Fri Dec 31 1999 23:59:59.000

Tue Jan 04 2000 00:00:00.000

Sat Jan 01 2000 12:01:00.000

Tue Jan 04 2000 00:00:00.000

Result

Column1 : FLOAT

0.0

1.0007291666666667

0.5007060185185186

3.0000115740740743

2.4993055555555554

[6] Calculate the difference between dates of five rows in months (equals MONTHS_BETWEEN).

Query

Column1

DATEDIFF ( mm , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Sat Jan 15 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.000

Mon Jan 31 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 2010 00:00:00.000

Mon Jan 31 2000 00:00:00.000

Fri Dec 31 2010 00:00:00.000

Result

Column1 : FLOAT

0.0

0.49282326125793136

1.0185014065997249

132.01092424895788

130.99242284235817

[7] Calculate the difference between dates of five rows in years (equals YEARS_BETWEEN).

Query

Column1

DATEDIFF ( yy , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Fri Dec 31 1999 23:59:59.000

Thu Jun 01 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.000

Sun Dec 31 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.000

Mon Dec 31 2001 00:00:00.000

Sun Dec 31 2000 00:00:00.000

Mon Dec 31 2001 00:00:00.000

Result

Column1 : FLOAT

0.0

0.41889977206924167

1.0020739645577939

2.001410022108599

0.9993360575508052