Is there a function that takes a year, month and day to create a date in PostgreSQL?

Question

In the docs I could only find a way to create a date from a string, e.g. DATE '2000-01-02'. This is utterly confusing and annoying. What I want instead is a function that takes three parameters, so I could do make_date(2000, 1, 2) and use integers instead of strings, and returns a date (not a string). Does PostgreSQL have such a built-in function?

The reason I'm asking this is because I dislike the use of strings for things that are not strings. Dates are not strings; they're dates.

The client library I use is HDBC-PostgreSQL for Haskell. I'm using PostgreSQL 9.2.2.

Consider

Is there a reason you can't use 2000||'-'||01||'-'||02? Those would be integers, but produce a string. – Lemuel

In future please always mention your PostgreSQL version. I don't think the downvotes are warranted, but you really should explain more - Pg version, what problem you're trying to solve, the language and client library you're using, sample code if possible, etc. – Beacon

Answer 1

Needing to do this in SQL routinely usually says you have problems with your data model where you're storing dates split up into fields in the DB rather than as true date or timestamp fields, or you have serious escaping and SQL injection problems. See explanation below.

Either of the following will solve your immediate problem:

CREATE OR REPLACE FUNCTION make_date(year integer, month integer, day integer) AS $$
SELECT year * INTERVAL '1' YEAR + month * INTERVAL '1' MONTH + day * INTERVAL '1' DAY;
$$ LANGUAGE sql STRICT IMMUTABLE;

or

CREATE OR REPLACE FUNCTION make_date(year integer, month integer, day integer) AS $$
SELECT format('%s-%s-%s', year, month, day)::date;
$$ LANGUAGE sql STRICT IMMUTABLE;

but please, read on.

---

The fact that you're asking this makes me think you're probably trying to build SQL in your application like this:

$sql = "SELECT date'" + year + '-' + month + '-' + day + "';";

which is generally dangerous and wrong (though probably not directly unsafe with if year, month and day are integer data types). You should be using parameterized queries instead if this is what you're doing to avoid SQL injection and save yourself lots of hassle with escaping and literal formatting. See http://bobby-tables.com/ .

Here's how you'd query a date using a parameterized statement in Python with psycopg2 (since you didn't specify your language or tools):

import datetime
import psycopg2
conn = psycopg2.connect('')
curs = conn.cursor()
curs.execute('SELECT %s;', ( datetime.date(2000,10,05), ))
print repr(curs.fetchall());

This will print:

[(datetime.date(2000, 10, 5),)]

ie an array with a single Python date in it. You can see that it's been on a round trip through the database and you've never had to worry about PostgreSQL's date format or representation, since psycopg2 and PostgreSQL take care of that for you when you use parameterized statements. See this earlier related answer.

Beacon

I know about SQL injection and prepared statements and it's not relevant to my problem. I just really dislike representing values as strings when they are not strings, especially dates since there are a gazillion different ways to format them as strings. – Consider

Heh, the last paragraph has a good point. I didn't notice client libraries supported binding dates directly. Have an upvote. – Consider

@Zoidberg For what its worth, in SQL a literal like DATE '2012-01-01' is not defined as a string, it's an SQL date literal as defined by the standard. It's a representation of a date as text, but so is datetime.date(2012,01,01), and it is just as well-defined. In any case, language bindings are certainly the way to go. – Beacon

This answer is out of date. Gregory Arenius supplied a better answer below. – Genitalia

Answer 2

In PostgreSQL 9.4 and greater there is actually a make_date(year int, month int, day int) function that will create a date.

https://www.postgresql.org/docs/current/functions-datetime.html

Plenipotent

The original accepted answer is out of date, this is definitely the right answer now. – Genitalia

Answer 3

Something else worth trying is the to_date() function. It is similar to the bindings mentioned above and there is no need to create a user defined functions.

http://www.postgresql.org/docs/current/static/functions-formatting.html

I use it in this form:

to_Date(month::varchar ||' '|| year::varchar, 'mm YYYY')

Lyceum

Answer 4

you can use date(concat())

select date(concat(2000::varchar, '-01-01'))

Moa