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

Question

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.

PSQL: 2014-04-06 10:22:57
JDBC: 2014-04-06 10:22:57:000
(driver postgresql-9.3-1101.jdbc4.jar run through the SQLExplorer eclipse plugin)
node-pg: Sun Apr 06 2014 10:22:57 GMT+0200 (CEST)

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?

timestamps are stored without any format in the database. The formatting is always done by the application displaying the data. Usually you can configure the SQL client to apply a specific format to timestamp (and date) columns. As long as you retrieve a real java.sql.Timestamp instance from the result set (e.g. using ResultSet.getTimestamp()) this client formatting does not matter. — user330315
– user330315, Commented Apr 6, 2014 at 9:32
@a_horse_with_no_name Yeah, I just wasn't expecting node-pg to parse it into Data object - I was expecting the raw string value. Read the docs, right? I'll just override the type parser. You don't by any chance know of a way to do that per connection or even per query? — BadIdeaException
– BadIdeaException, Commented Apr 6, 2014 at 10:25
I don't know node-pg so I don't know what a Data object is. But the output seems to be the result of calling toString() on a java.sql.Timestamp instance. Are you by any chance simply calling System.out.println() with an Object obtained from a ResultSet. If yes, then everything seems OK. Just work with the java.sql.Timestamp instance. — user330315
– user330315, Commented Apr 6, 2014 at 10:56
My bad, typo. I meant Date. Sorry for the confusion. I'm not using Java, the JDBC is just for an Eclipse plugin I'm using. I'm on JavaScript (node.js) — BadIdeaException
– BadIdeaException, Commented Apr 6, 2014 at 11:27

Craig Ringer · Accepted Answer · 2014-04-06 10:36:28Z

2

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.

answered Apr 6, 2014 at 10:36

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

9 Comments

BadIdeaException Over a year ago

That will be exceedingly difficult, because I'm querying generically for syncing: I have a table journal which holds changes to the database in the form sequence number|table_name|row|operation and based on that I'm building queries dynamically. So manipulating the queries would require a ton of code overhead.

Craig Ringer Over a year ago

You'll have to do it client-side by data type, then. Relying on the protocol format is really not wise.

BadIdeaException Over a year ago

Why do you consider it unwise?

Craig Ringer Over a year ago

See the last par of the answer. You're also dependent on what the client driver does. Beyond me why you don't just format dates client-side.

BadIdeaException Over a year ago

Because my synchronization algorithm is abstracted over the type of the data. It basically just reads the data as indicated by the journal and JSONs it to an Android app, where it gets written to the app's database. It's two way syncing, so the algorithms are basically mirror images of each other. Neither one lends itself easily to post processing the data.

|

Andrew Wolfe · Accepted Answer · 2015-06-10 15:10:54Z

0

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.

answered Jun 10, 2015 at 15:10

Andrew Wolfe

2,11620 silver badges28 bronze badges

4 Comments

sherpya Over a year ago

precision? why the hell I would store timezone data, then how should I display to users in a different timezone?

sherpya Over a year ago

I prefer sending/storing utc until the date needs to be displayed to the user

Andrew Wolfe Over a year ago

If you know it's UTC, why don't you tell the database about it?

sherpya Over a year ago

sending non utc date without time zone information is not an option, it's wrong, I suppose "without timezone" would only mean UTC

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related