Skip to main content

datediff

1. DATEDIFF Function​

The DATEDIFF function in H2 database is used to calculate the difference between two dates or timestamps in terms of a specified time unit.

2. Syntax​

The syntax for the DATEDIFF function in H2 database is as follows:

DATEDIFF(unit, start_date, end_date)

Arguments​

  • unit: The unit of time in which the difference needs to be calculated. It should be one of the following: 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND'.
  • start_date: The starting date or timestamp.
  • end_date: The ending date or timestamp.

Return​

  • The DATEDIFF function returns the difference between the end date and start date in terms of the specified unit.

3. Notes​

  • The DATEDIFF function in H2 database calculates the difference between two dates or timestamps based on the specified time unit. It does not consider the time component if the input values are timestamps.
  • If the input values are not valid dates or timestamps, the DATEDIFF function will return NULL.
  • The DATEDIFF function considers the calendar when calculating the difference between two dates, including leap years and month lengths.
  • Make sure to use the correct syntax and provide valid date or timestamp values to avoid errors.

4. Examples​

Here are a few examples demonstrating the usage of the DATEDIFF function in H2 database:

Example 1 - Calculating the difference in days between two dates:​

SELECT DATEDIFF('DAY', DATE '2021-01-01', DATE '2021-01-05') AS diff_days;

Output:

diff_days
---------
4

Example 2 - Calculating the difference in months between two timestamps:​

SELECT DATEDIFF('MONTH', TIMESTAMP '2021-01-01 10:00:00', TIMESTAMP '2021-03-15 15:30:00') AS diff_months;

Output:

diff_months
-----------
2
  • dateadd - Add a specified time interval to a date or timestamp
  • TIMESTAMPDIFF - Calculate the difference between two timestamps in terms of a specified time unit