1

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.

Improve this question
1
  • So please add you findings as an answer, and accept it, so this question gets closed with an accepted answer. Commented Feb 21, 2011 at 17:40

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

But the funny thing is, my problem is that I'm looking at the same columns, just with two different programs. I get a different output format depending on the PROGRAM. Which tells me that one of the programs must be doing something to change the format.
How do you know that the program doesn't format the date format the date value itself? The ONLY official program is psql, all other software retrieves the values as date values and it is legic for the software to format it at will.
What does the differing output look like?
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

Improve this answer

4 Comments

I'm using the JDBC interface in a Java program. When I say "formatting the output", I mean what I get when I read the timestamp as a string. That is, I run "select transactiondate from ...", then in Java I use ResultSet.getString on the field, and I get the different format depending on which program runs the query.
Yes, before posting the question I found the "set datestyle" call, but that doesn't seem to be it. That only gives four format choices, and none of them have a fractional second and no time zone. Or am I missing something?
I don't think ResultSet.getString() is influenced by the session settings from Postgres. If you do need the timestamp as a character value I suggest you use to_char() in your query to control the format. Or use ResultSet.getTimestamp() and use a SimpleDateFormat to format it inside your Java program
a_horse_with_no_name: See my answer to my own question. Briefly: Yes, I think you're correct, there is no setting to alter the format like this. I was confused about where the formatting was being done.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.