Skip to main content

date_trunc

1. DATE_TRUNC Function​

The DATE_TRUNC function in H2 database is used to truncate a date or timestamp value to a specified unit of time.

2. Syntax​

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

DATE_TRUNC(unit, date)

Arguments​

  • unit: The unit of time to which the date or timestamp value should be truncated. It should be a string value representing the unit, such as 'year', 'month', 'day', 'hour', 'minute', or 'second'.
  • date: The date or timestamp value to be truncated. It should be a valid date or timestamp value.

Return​

  • The DATE_TRUNC function returns a new date or timestamp value with the specified unit of time truncated.

3. Notes​

  • The DATE_TRUNC function in H2 database is similar to the equivalent function in other databases like PostgreSQL and Oracle.
  • The unit parameter can be any valid unit of time, but note that not all units are supported in H2 database. Refer to the official H2 database documentation for a list of supported units.
  • The truncation of the date or timestamp value is done based on the unit provided. For example, if the unit is 'year', the function will truncate the date or timestamp to the beginning of the year.
  • The DATE_TRUNC function can be useful for grouping or aggregating data based on specific time periods.

4. Examples​

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

Example 1 - Truncating a timestamp to the nearest hour:​

SELECT DATE_TRUNC('hour', TIMESTAMP '2022-04-15 10:23:45') AS truncated_time;

Output:

truncated_time
---------------------
2022-04-15 10:00:00

Example 2 - Truncating a date to the nearest month:​

SELECT DATE_TRUNC('month', DATE '2022-03-18') AS truncated_date;

Output:

truncated_date
--------------
2022-03-01
  • DATE_ADD - Add intervals to dates or timestamps
  • DATE_DIFF - Calculate the difference between dates or timestamps