change postgres date format

Posted by Jay on Stack Overflow See other posts from Stack Overflow or by Jay
Published on 2011-02-21T05:12:25Z Indexed on 2011/02/21 7:24 UTC
Read the original article Hit count: 162

Filed under:
|
|

Is there a way to change the default format of a date in Postgres?

Normally when I query a Postgres database, dates come out as yyyy-mm-dd hh:mm:ss+tz, like 2011-02-21 11:30:00-05.

But one particular program the dates come out yyyy-mm-dd hh:mm:ss.s, that is, there is no time zone and it shows tenths of a second.

Apparently something is changing the default date format, but I don't know what or where. I don't think it's a server-side configuration parameter, because I can access the same database with a different program and I get the format with the timezone.

I care because it appears to be ignoring my "set timezone" calls in addition to changing the format. All times come out EST.

Additional info:

If I write "select somedate from sometable" I get the "no timezone" format. But if I write "select to_char(somedate::timestamptz, 'yyyy-mm-dd hh24:mi:ss-tz')" then timezones work as I would expect.

This really sounds to me like something is setting all timestamps to implicitly be "to_char(date::timestamp, 'yyyy-mm-dd hh24:mi:ss.m')". But I can't find anything in the documentation about how I would do this if I wanted to, nor can I find anything in the code that appears to do this. Though as I don't know what to look for, that doesn't prove much.

© Stack Overflow or respective owner

Related posts about sql

Related posts about date