Geek Answers Handbook

Cannot enter date in postgres field with data type timestamp

I am trying to insert a date ("This is a string") in the postgres database field. I get the following error:

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 how to do this?

+4
source share
3 answers

You are trying to insert a timestamp. You need to combine time with date. From the Postgres documentation :

Valid input for timestamp types consists of a concatenation of the date and time, followed by an optional time zone, followed by an additional AD or BC. (Alternatively, AD / BC may be in front of the time zone, but this is not the preferred order.) Thus,

1999-01-08 04:05:06

and

1999-01-08 04:05:06 -8:00

Values ​​that comply with ISO 8601 standard are valid.

make:

 $date = '2002-03-11 12:01AM';
+9
source

I'm not sure why you are getting this error, while the value in quotation marks is empty. PostgreSQL, of course, is great for dates without times in timestamp fields. They default to the time "00:00:00", the beginning of this day.

 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 few things you are doing here, but this is not entirely correct, and one of them can improve your situation:

  • Using a “quote” instead of prepared statements in a real application is the first step to allowing SQL injection into your code, which leads to a bad habit. The fact that you get an error message makes me wonder if the PDO is doing something wrong in the middle here, which would be less likely if you prepared the statement and passed the value more directly through.

  • You really should use this directly from a string in a timestamp, and not depend on implicit casting. Example in direct SQL:

     INSERT INTO dates (date) VALUES (cast('2002-03-11' as timestamp));

  • 'date' is an SQL reserved word . You should not specify such fields because you may need to wrap the quotation marks around the name in order for them to be parsed correctly.

+5
source

Your error apparently clearly states what the problem is:

 ERROR: invalid input syntax for type timestamp: ""

It looks like your query is trying to insert an empty string into a PostgreSQL field that is of type "timestamp". If you insert an invalid string of any type, it should appear in the error you receive:

 ERROR: invalid input syntax for type timestamp: "foobardtimestamp"

Or, in your case, if your expected string was passed, your error might look like this:

 ERROR: invalid input syntax for type timestamp: "2002-03-11"

... but the error does not say what makes me suspect that your string is not actually passed to the request, as you think. The fact is that, as stated earlier, PostgreSQL should perfectly handle 2002-03-11 as a valid timestamp string.

PostgreSQL does not like to insert '' (empty string) as a timestamp and will complain about the error you provided.

If you want to provide an empty row, you must be sure that you do not have a NOT NULL restriction on the column, and you need to use null instead of the empty row. If you don't want to send an empty string, I would check the value of $pdo->quote($date) to make sure you get the string you want to return.

You can also try to dump the generated SQL before you run the query to make sure it looks right. I have a feeling if you do this, it will look something like this:

 INSERT INTO dates(date) VALUES('')

Also, for what it's worth, your example says you're working: $pdo->query($date); when I'm sure you want: $pdo->query($query);

+5
source

More articles:


All Articles