Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

DATEDIFF

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

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