change postgres date format

Question

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.

Never mind :'(

I found my problem. I was thinking that I was looking directly at the string coming back from the database. But I was overlooking that it was reading it as a Timestamp and then converting the Timestamp to a string. This was buried inside a function called "getString", which is what threw me off. I was thinking it was ResultSet.getString, but it was really our own function with the same name. Oops. What idiot wrote that function?! Oh, it was me ...

Thanks to all who tried to help. I'll give you each an upvote for your trouble.

Answer 1

Daniel tells me to post my findings as an answer and accept it to close the question. Okay.

I found that the date format I was seeing that did not include a time zone was not what was coming directly from Postgres, but that there were a couple of function calls that I was missing that converted the incoming date to a java.util.Timestamp, and then from the java.util.Timestamp to a String. It was in this conversion from the Timestamp to the String that the time zone was defaulting to EST.

In my own humble defense, my mistake was not as dumb as it may sound. :-0 We had the execution of the query in a subclass that read the results into a List, which we do to allow modification of the query results before output. (In this case we are adding a coule of columns that are derived from the stored columns.) Then we have a set of functions that resemble the JDBC functions to pull the data out of the List, so a calling program can easily switch from processing a query directly to processing the List. When I was wrestling with the date format problem, it just didn't register on me that I wasn't looking at "real JDBC", but at "simulated JDBC" calls.

Answer 2

With which client are you running the select statements? Formatting the output is the application's responsibility, so without knowing which application you use to display the data, it's hard to tell.

Assuming you are using psql, you can change the date format using the SET command:
http://www.postgresql.org/docs/current/static/sql-set.html

Which is essentially a way to change the configuration parameters. The ones that are responsible for formatting data are documented here:
http://www.postgresql.org/docs/current/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

Answer 3

I believe the table columns are specified differently. Try these variants:

timestamp
timestamp(0)   no millis
timestamptz    with timezone
timestamptz(0) with timezone, no millis