PostgreSQL: SELECT integers as DATE or TIMESTAMP
date, postgresql, sql
Solution
You can
SELECT format('%s-%s-%s', "year", "month", "day")::date
FROM ...
or use date maths:
SELECT DATE '0001-01-01'
+ ("year"-1) * INTERVAL '1' YEAR
+ ("month"-1) * INTERVAL '1' MONTH
+ ("day"-1) * INTERVAL '1' DAY
FROM ...
Frankly, it's surprising that PostgreSQL doesn't offer a date-constructor like you describe. It's something I should think about writing a patch for.
In fact, a quick look at the sources shows that there's an `int date2j(int y, int m, int d)` function at the C level already, in `src/backend/utils/adt/datetime.c`. It just needs to be exposed at the SQL level with a wrapper to convert to a `Datum`.
OK, now here's a simple `makedate` extension that adds a single function implemented in C, named `makedate`. A pure-SQL version is also provided if you don't want to compile and install an extension. I'll submit the C function for the 9.4 commitfest; meanwhile that extension can be installed to provide a fast and simple date constructor:
regress=# SELECT makedate(2012,01,01);
makedate
------------
2012-01-01
(1 row)
Problem
I have a table where I have multiple integer columns: year, month and day. Unfortunately, while the three should have been grouped into one DATE column from the beginning, I am now stuck and now need to view it as such. Is there a function that can do something along the lines of: ``` SELECT makedate(year, month, day), othercolumn FROM tablename; ``` or ``` SELECT maketimestamp(year, month, day, 0, 0), othercolumn FROM tablename; ```