/
DATE_DIFF()

DATE_DIFF()

DATE_DIFF(date, subtract, [unit])

Calculate the difference between 2 dates and returns the result in the specified units (days, hours...).

It is possible to provide only the time part (without date), and then the following rules apply:

  • If the date part of date is not present, assume that it corresponds to the current datetime (in the session timezone).

  • If the time part of subtract is not present, then assume that it corresponds to the same date than date, except when the time is bigger than the time of the date. In the latter case, consider that the date to subtract is in the previous day.

For example, to calculate the time in bed of a person that went to sleep at 22:00 and woke up the next day at 08:00, we would subtract 08:00 - 22:00, and the result would be 10h (because 22:00 is considered to be in the previous day)

Input parameters

Parameters

Type

Default

Required

Description

Default

Parameters

Type

Default

Required

Description

Default

date

String

 

Yes

Base date: a string specifying a valid datetime (e.g. "2019-11-01 12:00:34") or time (e.g. "23:00"). If the date part is not provided, then the current date (in the session timezone) will be assumed.

 

subtract

String/Number

 

Yes

Value to subtract the date.Two types of value are accepted

  • a string specifying a valid datetime (e.g. "2019-11-01 12:00:34") or time (e.g. "23:00"). If the date part is not provided, then the same date than the date parameter will be assumed.

  • Numeric value (deprecated): This type of value is supported only for backward compatibility. It represents an integer number of days to subtract to the date provided. The return value is a date time formatted string (e.g. "2019-11-10"). You should use DATE_ADD() to perform this type of calculations. Note that in this case, the time part of the date will be ignored

 

unit

String

"D"

No

Units in which the return value will be expressed:

  • "s" : integer number of seconds

  • "m": integer number of minutes

  • "M": minutes including decimal part

  • "h" : integer number of hours

  • "H": hours including decimal part

  • "d": integer number of days

  • "D": days including decimal part

  • "TIME": format output as a time string ("hh:mm:ss"). Note that if the difference is more than one day, the number of hours will be larger than 24

  • "SHORT_TIME": format output as a time string without seconds ("hh:mm"). Note that if the difference is more than one day, the number of hours will be larger than 24

If no unit is specified, then "d" is the default value (integer number of days)

If SUBTRACT is expressed as a number, then this parameter is ignored and the return value will be a date time formatted string

 

Return value

Type

Description

Type

Description

Number

Returns the difference between the dates in the requested units.

Note that decimal values can be returned (e.g. 2.5 days)

Execution

Always. Calculated on-the-fly whenever an ITEM of a FORM is changed (by means of form_set_answer(), or when the FORM is opened

Examples

Formula

Description

Formula

Description

DATE_DIFF("2019-05-12", "2019-05-11")

No UNIT specified, so "D" is assumed. The final result would be 1 (day)

DATE_DIFF("2019-05-12 12:00:00", "2019-05-11")

No UNIT specified, so "D" is assumed. The difference returns a decimal value. The final result would be 1.5 (day)

DATE_DIFF("2019-05-12 12:00:00", "2019-05-11", "h")

Return requested in hours. The final result would be 36 (hours)

DATE_DIFF("08:00:00", "23:00", "h")

Only the time part is provided. The calculation will consider that the date to subtract belongs to the previous day because the time to subtract is bigger that the base time

DATE_DIFF("2019-05-12", "2019-05-10 12:00:00", "TIME")

Subtract two dates and return the result formatted as a time string. The result would be "36:00:00"

DATE_DIFF("2019-05-12", 3)

Legacy support. Subtracts 3 days from the date and returns a string with the final date.

The result would be "2019-05-09".

You should use DATE_ADD() instead of DATE_DIFF() to perform this kind of calculations