PHP/PDO can't bind 'TIMESTAMP WITHOUT TIME ZONE' values

Question

I'm trying to prepare and execute a query against my PostgreSQL database using PHP's PDO library, and it isn't working as expected.

When using the command line psql client, the following works as one would expect:

=> SELECT TIMESTAMP WITHOUT TIME ZONE '2013-01-01 00:00:00';

      timestamp      
---------------------
 2013-01-01 00:00:00
(1 row)

However, I don't seem to be able to do the same using PHP/PDO. The following code:

<?php
try
{
    $db = new PDO(...);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $db->prepare("SELECT TIMESTAMP WITHOUT TIME ZONE ?;");

    $stmt->bindValue(1, "2013-01-01 00:00:00", PDO::PARAM_STR);
    $stmt->execute();

    $row = $stmt->fetch();

    echo $row[0];
}
catch(PDOException $e)
{
    die($e->getMessage());
}
?>

Produces the following error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" LINE 1: SELECT TIMESTAMP WITHOUT TIME ZONE $1; ^

I am aware of PostgreSQL's to_timestamp() function, which does work, but it returns a TIMESTAMP WITH TIME ZONE instead - the problem being that to_timestamp isn't an immutable function (i.e., to not return different results based on the sever's timezone configuration).

Similarly, binding the following does what I want, and can be prepared successfully:

SELECT to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP WITHOUT TIME ZONE;

But I'm hoping for something cleaner / less verbose.

Am I simply preparing my statement incorrectly or, if this will never work, what is an alternative way to go from a string in my application to a TIMESTAMP WITHOUT TIME ZONE in PostgreSQL?

Answer 1

Try casting it directly, with parenthesis to avoid confusing PDO/Postgres:

(?)::timestamp