php 无法使用数据类型时间戳在 postgres 字段中输入日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1311765/
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
Can't enter date into postgres field with datatype timestamp
提问by Elitmiar
I'm trying to insert a date ("This is a string") into a postgres database field. I'm getting the following error
我正在尝试将日期（“这是一个字符串”）插入到 postgres 数据库字段中。我收到以下错误
ERROR: invalid input syntax for type timestamp: ""
Here is my code
这是我的代码
$date = '2002-03-11';
$query = 'INSERT INTO dates(date) VALUES('.$pdo->quote($date).')';
$pdo->query($date);
I have absolutely no idea on how to do this?
我完全不知道如何做到这一点？
回答by Eric
You're trying to insert into a timestamp. You need to concatenate the time with the date. From the Postgres documentation:
您正在尝试插入时间戳。您需要将时间与日期连接起来。从Postgres 文档：
Valid input for the time stamp types consists of a concatenation of a date and a time, followed by an optional time zone, followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the preferred ordering.) Thus
1999-01-08 04:05:06and
1999-01-08 04:05:06 -8:00are valid values, which follow the ISO 8601 standard.
时间戳类型的有效输入包括日期和时间的串联，后跟可选的时区，后跟可选的 AD 或 BC。（或者，AD/BC 可以出现在时区之前，但这不是首选顺序。）因此
1999-01-08 04:05:06和
1999-01-08 04:05:06 -8:00是符合 ISO 8601 标准的有效值。
Do:
做：
$date = '2002-03-11 12:01AM';
回答by Greg Smith
I'm not sure why you're getting that error, with the value in the quotes empty like that. PostgreSQL certainly accepts dates without times into timestamp fields just fine. They default to a time of "00:00:00", the start of that day.
我不确定您为什么会收到该错误，引号中的值是空的。PostgreSQL 当然接受没有时间的日期到时间戳字段就好了。它们默认为“00:00:00”的时间，即当天的开始。
CREATE TABLE dates("date" timestamp);
INSERT INTO dates (date) VALUES ('2002-03-11');
SELECT * from dates;
date
---------------------
2002-03-11 00:00:00
There are a couple of things you're doing here that aren't quite right though, and one of them might improve your situation:
不过，您在这里做的有几件事不太正确，其中之一可能会改善您的情况：
- Using 'quote' instead of prepared statements in a real application is a first step toward allowing SQL injection attacks into your code, that's a bad habit to get into. The fact that you're getting an error here makes me wonder if PDO is doing something wrong in the middle here, which would be less likely to happen if you prepared the statement and passed the value more directly through.
You really should cast this directly from string to timestamp rather than depend on the implicit casting here. Example in straight SQL:
INSERT INTO dates (date) VALUES (cast('2002-03-11' as timestamp));'date' is a SQL reserved word. You shouldn't name fields like that, because you can end up needing to wrap references to the name in quotes for them to be parsed correctly.
- 在实际应用程序中使用“引用”而不是准备好的语句是允许 SQL 注入攻击到您的代码的第一步，这是一个坏习惯。您在这里遇到错误这一事实让我怀疑 PDO 是否在中间做错了什么，如果您准备好语句并更直接地传递值，则不太可能发生这种情况。
您确实应该直接将其从字符串转换为时间戳，而不是依赖于此处的隐式转换。直接 SQL 中的示例：
INSERT INTO dates (date) VALUES (cast('2002-03-11' as timestamp));'date' 是一个 SQL保留字。您不应该这样命名字段，因为您最终可能需要将对该名称的引用用引号括起来，以便正确解析它们。
回答by Greg Smith
Your error seems to clearly state what the problem is:
您的错误似乎清楚地说明了问题所在：
ERROR: invalid input syntax for type timestamp: ""
It appears your query is trying to insert an empty string into a PostgreSQL field that has a type of 'timestamp'. If you were inserting an invalid string of some sort, it should appear in the error that you are recieving like:
您的查询似乎正在尝试将空字符串插入到具有“时间戳”类型的 PostgreSQL 字段中。如果您插入了某种无效字符串，它应该出现在您收到的错误中，例如：
ERROR: invalid input syntax for type timestamp: "foobardtimestamp"
Or, in your case, if your expected string was being passed, your error may look like this:
或者，在您的情况下，如果您的预期字符串被传递，您的错误可能如下所示：
ERROR: invalid input syntax for type timestamp: "2002-03-11"
...but the error doesn't say that which makes me suspect your string isn't actually getting passed to the query like you think. The fact is, as previously pointed out: PostgreSQL should be perfectly capable of handling 2002-03-11as a valid timestamp string.
...但错误并没有说明这让我怀疑您的字符串实际上并没有像您想象的那样传递给查询。事实是，正如之前所指出的：PostgreSQL 应该完全有能力处理2002-03-11一个有效的时间戳字符串。
PostgreSQL doesn't like inserting '' (empty string) as a timestamp and will complain with the error that you provided.
PostgreSQL 不喜欢插入 '' （空字符串）作为时间戳，并且会抱怨您提供的错误。
If you want to provide an empty string, you need to be sure you don't have a NOT NULLconstraint on the column, and you need to use nullinstead of an empty string. If you aren't meaning to send an empty string, I would check on the value of $pdo->quote($date)to make sure you're getting the string that you want returned from that.
如果要提供空字符串，则需要确保NOT NULL对列没有约束，并且需要使用null而不是空字符串。如果您不打算发送空字符串，我会检查 的值$pdo->quote($date)以确保您获得了想要从中返回的字符串。
You can also try to output your generated SQL before you actually run the query to make sure it looks correct. I have a feeling, if you do, it will look something like this:
您还可以尝试在实际运行查询之前输出生成的 SQL，以确保它看起来正确。我有一种感觉，如果你这样做了，它看起来像这样：
INSERT INTO dates(date) VALUES('')
Also, for what it's worth, your example says you're running:
$pdo->query($date);when I'm fairly certain you want: $pdo->query($query);
此外，对于它的价值，您的示例说您正在运行：
$pdo->query($date);当我相当确定您想要时：$pdo->query($query);
