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?

1
5
12/25/2012 2:12:39 AM

Accepted Answer

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';
9
8/21/2009 1:00:46 PM

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


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon