sqlpostgresqldate

How to define a custom type for two dates and extract years from it in PostgreSQL?


I want to create a custom type in PostgreSQL that combines two DATE fields (e.g., start_date and end_date).

And I want a SELECT query to extract just the year parts from these fields.

Given the following data:

range (my_custom_type)
(2010-01-01,2020-01-01)

The desired query result should be:

start_year (numeric) end_year (numeric)
2010 2020

Update (after almost 8 years)

I don’t see a practical use case for creating a custom type to combine two dates.
The original problem could likely have been resolved using the DATERANGE type.

Here’s how it could be implemented using DATERANGE:

CREATE TABLE date_ranges(range DATERANGE NOT NULL);

INSERT INTO date_ranges VALUES ('[2010-01-01, 2020-01-01]');

SELECT EXTRACT(YEAR FROM LOWER(range)) AS start_year,
       EXTRACT(YEAR FROM UPPER(range)) AS end_year
FROM date_ranges;

Solution

  • SQL DEMO

    CREATE TYPE twoYears AS
       (year1 date,
        year2 date);
    
    DROP TABLE IF EXISTS myTable;  
    
    CREATE TABLE myTable (
       ty twoYears
    );    
    
    INSERT INTO myTable VALUES (ROW('2010-01-01', '2020-01-01'));
    
    SELECT (ty).year1,  (ty).year2
    FROM myTable;
    
    SELECT EXTRACT(YEAR FROM (ty).year1),
           EXTRACT(YEAR FROM (ty).year2)
    FROM myTable;