Skip to main content

extract

1. EXTRACT Function​

The EXTRACT function in H2 database is used to extract a specific part (such as year, month, day, etc.) from a date or timestamp value.

2. Syntax​

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

EXTRACT(part FROM date)

Arguments​

  • part: The part of the date or timestamp value to be extracted. It can be one of the following:

    • YEAR
    • MONTH
    • DAY
    • HOUR
    • MINUTE
    • SECOND
    • MILLISECOND

  • date: The date or timestamp value from which the part needs to be extracted. It should be a valid date or timestamp value.

Return​

  • The EXTRACT function returns the specified part of the date or timestamp value as an integer.

3. Notes​

  • The EXTRACT function in H2 database is commonly used to extract specific components from dates or timestamps for further analysis or calculations.
  • The part argument should be written in uppercase letters.
  • If the input date or timestamp value is NULL, the EXTRACT function will return NULL.
  • Make sure to use the correct syntax and provide a valid date or timestamp value to avoid errors.

4. Examples​

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

Example 1 - Extracting the year from a date:​

SELECT EXTRACT(YEAR FROM DATE '2022-01-15') AS year;

Output:

year
----
2022

Example 2 - Extracting the month from a timestamp:​

SELECT EXTRACT(MONTH FROM TIMESTAMP '2022-01-15 09:30:45') AS month;

Output:

month
-----
1

Example 3 - Extracting the hour from a timestamp stored in a column:​

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_time TIMESTAMP
);

INSERT INTO events VALUES (1, TIMESTAMP '2022-01-15 09:30:45'),
                          (2, TIMESTAMP '2022-02-20 18:45:15');

SELECT id, event_time, EXTRACT(HOUR FROM event_time) AS hour
FROM events;

Output:

id |         event_time         | hour
---+----------------------------+------
1  | 2022-01-15 09:30:45.000000 | 9
2  | 2022-02-20 18:45:15.000000 | 18
  • year - Extract year
  • month - Extract month
  • DAY - Extract day
  • hour - Extract hour
  • minute - Extract minute
  • second - Extract second
  • MILLISECOND - Extract millisecond