PostgreSQL: SELECT integers as DATE or TIMESTAMP

Question

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;

Answer 1

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)

Answer 2

PostgreSQL 9.4+

In PostgreSQL 9.4, a function was added to do just this

• make_date(year int, month int, day int)