Is there a function that takes a year, month and d

问题:

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.

回答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.

回答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.

http://www.postgresql.org/docs/9.5/static/functions-datetime.html

回答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')