PostgreSQL: how to format date without using to_char()?

I have this PHP-script which I'd like to keep unchanged:

$display = array(
    'QDATETIME',
    'ID',
    'NAME',
    'CATEGORY',
    'APPSVERSION',
    'OSVERSION',
);

$sql = sprintf("
    select %s from quincytrack
  开发者_JAVA百科  where qdatetime > (now() - interval '2 day') and
    qdatetime <= now()",
    join(', ', $display));
$sth = $pg->prepare($sql);
$sth->execute($args);

My problem is that QDATETIME is printed as 2010-12-18 15:51:37 while I need it as 2010-12-18. I know I could call to_char(QDATETIME, 'YYYY-MM-DD') as QDATETIME, but would prefer to find some other way to affect the date output format...

Using PostgreSQL 8.4.5 under CentOS 5.5 Linux / 64 bit.

Thank you! Alex

A better approach than relying on the database for date formatting would be to rely on PHP for this.

echo date("Y-m-d", strtotime($row['column']));

This both solves your initial problem (of confusing the join statement when building your query), and also gives you a lot more flexibility (eg, maybe later a user can set a preference for preferred date format), and more scalability (the more work you can do in PHP which is unlimitedly horizontally scalable, the better).

Just CAST to datatype DATE:

SELECT
    CAST('2010-12-18 15:51:37' AS DATE);

If you don't want to change your PHP script, you can try adjusting datestyle configuration parameter.

I would recommend that you just change your PHP script though. Changing a global server configuration might affect other stuff currently running on your server.