是否存在需要花费一年，几个月和一天才能在PostgreSQL中创建日期的函数？ [英] Is there a function that takes a year, month and day to create a date in PostgreSQL?

问题描述

在文档中，我只能找到一种从字符串创建日期的方法，例如日期 2000-01-02 。这是完全令人困惑和烦人的。我想要的是一个带有三个参数的函数，所以我可以做 make_date（2000，1，2）并使用整数而不是字符串，然后返回一个日期（不是一个字符串）。 PostgreSQL是否具有这样的内置功能？

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.

我使用的客户端库是适用于Haskell的HDBC-PostgreSQL。我使用的是PostgreSQL 9.2.2。

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

推荐答案

通常需要在SQL中执行此操作通常表示您的数据模型有问题您将日期存储在数据库中的各个字段中，而不是真实的日期或时间戳记字段中，或者您存在严重的转义和SQL注入问题。请参阅下面的说明。

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;

或

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;

但请继续阅读。

您所问的事实使我认为您可能正在尝试在应用程序中构建SQL，如下所示：

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 + "';";

通常是危险和错误（尽管如果年，月和天是整数数据类型）。如果这样做是为了避免 SQL，您应该使用参数化查询注入，并通过转义和原义格式节省了很多麻烦。参见 http://bobby-tables.com/ 。

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/ .

以下是使用带有psycopg2的Python中的参数化语句查询日期的方法（因为未指定语言或工具）：

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());

将打印：

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

即其中具有单个Python日期的数组。您可以看到它一直在数据库中进行往返，您不必担心PostgreSQL的日期格式或表示形式，因为当您使用参数化语句时，psycopg2和PostgreSQL会为您处理这些事情。参见此先前的相关答案。

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.

这篇关于是否存在需要花费一年，几个月和一天才能在PostgreSQL中创建日期的函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！