Can't enter date into postgres field with datatype timestamp

Question

I'm trying to insert a date ("This is a string") into a postgres database field. I'm getting 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 on how to do this?

Answer 1

You're trying to insert into a timestamp. You need to concatenate the time with the date. From the Postgres documentation:

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:06

and

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

are valid values, which follow the ISO 8601 standard.

Do:

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

Answer 2

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.

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.

Answer 3

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:

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-11 as a valid timestamp string.

PostgreSQL doesn't like inserting '' (empty string) as a timestamp and will complain with the error that you provided.

If you want to provide an empty string, you need to be sure you don't have a NOT NULL constraint on the column, and you need to use null instead 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.

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:

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