在 PostgreSQL 中是否有需要年月日来创建日期的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15317807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a function that takes a year, month and day to create a date in PostgreSQL?
提问by Gregory Arenius
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?
在文档中，我只能找到一种从字符串创建日期的方法，例如DATE '2000-01-02'. 这完全令人困惑和烦人。我想要的是一个带有三个参数的函数，所以我可以make_date(2000, 1, 2)使用整数而不是字符串，并返回一个日期（而不是字符串）。PostgreSQL 有这样的内置函数吗？
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.
我使用的客户端库是用于 Haskell 的 HDBC-PostgreSQL。我正在使用 PostgreSQL 9.2.2。
采纳答案by Craig Ringer
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.
通常需要在 SQL 中执行此操作通常表示您的数据模型存在问题，其中您将日期拆分为数据库中的字段而不是真实的日期或时间戳字段，或者您有严重的转义和 SQL 注入问题。请参阅下面的说明。
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，如下所示：
$sql = "SELECT date'" + year + '-' + month + '-' + day + "';";
which is generally dangerousand wrong(though probably not directly unsafe with if year, monthand dayare integer data types). You should be using parameterized queriesinstead if this is what you're doing to avoid SQL injectionand save yourself lots of hassle with escaping and literal formatting. See http://bobby-tables.com/.
这通常是危险和错误的（虽然可能不是直接不安全的 if year，month并且day是整数数据类型）。如果这是为了避免SQL 注入并为自己节省大量转义和文字格式的麻烦，那么您应该改用参数化查询。请参阅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):
以下是使用 psycopg2 在 Python 中使用参数化语句查询日期的方法（因为您没有指定语言或工具）：
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.
即一个包含单个 Python 日期的数组。您可以看到它一直在数据库中进行往返，而且您从来不必担心 PostgreSQL 的日期格式或表示形式，因为当您使用参数化语句时，psycopg2 和 PostgreSQL 会为您处理这些问题。请参阅此较早的相关答案。
回答by Gregory Arenius
In PostgreSQL 9.4 and greater there is actually a make_date(year int, month int, day int)function that will create a date.
在 PostgreSQL 9.4 及更高版本中，实际上有一个make_date(year int, month int, day int)函数可以创建日期。
http://www.postgresql.org/docs/9.5/static/functions-datetime.html
http://www.postgresql.org/docs/9.5/static/functions-datetime.html
回答by user2027647
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.
另一个值得尝试的是to_date()函数。它类似于上面提到的绑定，不需要创建用户定义的函数。
http://www.postgresql.org/docs/current/static/functions-formatting.html
http://www.postgresql.org/docs/current/static/functions-formatting.html
I use it in this form:
我以这种形式使用它：
to_Date(month::varchar ||' '|| year::varchar, 'mm YYYY')
