CODEWITHSUNDEEP
postgresqldatetime
BadIdeaException
BadIdeaException

Reputation: 2124

PostgreSQL: different output for timestamp without time zone depending on client

I'm puzzled by my PostgreSQL database returning different results for a timestamp(0) without time zone field. Let's say I have a table t

create table t (
    x timestamp(0) without time zone
);
insert into t select now();

Now when I run select * from t from different clients, I get different outputs.

Of the three, only PSQL is the output I expected (and require). Why is this happening, and how can I fix it without having to explicitly cast in the query?

Upvotes: 0

Views: 5638

Answers (2)

Andrew Wolfe
Andrew Wolfe

Reputation: 2096

Convert the database representation to TIMESTAMP WITH TIME ZONE. Period. It doesn't change the storage, it adds precision. Without the time zone, the precision of TIMESTAMP is +/- 24 hours.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324901

If you want a particular string format for a timestamp to be returned by the database, you should generally request it in your queries.

to_char(the_timestamp_col, 'YYYY-MM-DD HH:MI:SS')

See to_char in the docs.

Alternately, you can use the client application language's features to format the timestamp as you desire. This is usually the better option for more feature rich languages.

While PostgreSQL usually returns a specific text format for dates over the query protocol, clients are free to choose to use the binary protocol instead, in which case they'll get a wide integer value in epoch seconds. Rather than relying on the value in the PostgreSQL protocol being formatted how you want, it's much better to ask for exactly what you require.

Upvotes: 2

Related Questions