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

Question

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

TO_CHAR is specially designed for formatting the output. Why don't you want to use it? — Quassnoi
– Quassnoi, Commented Dec 20, 2010 at 14:42

MightyE · Accepted Answer · 2010-12-20 14:45:40Z

3

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

answered Dec 20, 2010 at 14:45

MightyE

2,67918 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2010-12-20 14:41:57Z

2

Just CAST to datatype DATE:

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

answered Dec 20, 2010 at 14:41

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

Quassnoi Over a year ago

This depends on the datestyle setting.

Pablo Santa Cruz · Accepted Answer · 2010-12-20 14:45:35Z

0

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.

answered Dec 20, 2010 at 14:45

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related