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;
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;