PostgreSQL and Hibernate: How do I format timestamp output?

Question

I have a Postgres DB, with a column named t_time of type timestamp with time zone.

When I use the psql tool to see the contents of the column, I get, for example:

select t_time from table1 where  ...

       t_time      
---------------------
 2011-02-28 14:09:25
 2011-02-28 14:09:23

This is the format I want.

However, when I run the same select query through my Java code, which uses Hibernate, I get the following:

 2011-02-28 14:09:25.0
 2011-02-28 14:09:23.0

As you can see, at the end a dot and a zero are added.

My question is - how do I specify the format I want to use?

Changing the query, for example like this,

select to_char(t_time, 'YYYY-MM-DD HH24:MI:ss') as t_time from table1 where ...

is possible, but less desired since I have a few places in the code that query for the data and I do not wish to change each of them, I would like the definition to be system-wide.

Thanks for your help.

EDIT:

I wanted to add a clarification: When I wrote this, I was thinking of the direction of editing the mapping file. I thought that there might be a way to define the format there. That would mean changing in one place only, and not relying on any default format.

Thanks.

Are you happy to change your postgres schema, seeing as you do not want to change your code? — user533832
– user533832, Commented Feb 28, 2011 at 14:25
@JackPDouglas: no, I'd rather change the code on several locations than change the schema. — Dikla
– Dikla, Commented Mar 1, 2011 at 6:55

splash · Accepted Answer · 2011-02-28 14:16:31Z

3

I think the use of a date formatter is always a good idea.

Date t_time = table1Entity.t_time;
String t_time_str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(t_time);

answered Feb 28, 2011 at 14:16

splash

13.4k1 gold badge49 silver badges69 bronze badges

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

2 Comments

user533832 Over a year ago

the question says "I have a few places in the code that query for the data and I do not wish to change each of them"

splash Over a year ago

@JackPDouglas: Thanks for the hint. I've overlooked that. But the answer might be helpful to anyone who is overlooking that too. ;-)

jpkroehling · Accepted Answer · 2011-02-28 16:43:56Z

1

is possible, but less desired since I have a few places in the code that query for the data and I do not wish to change each of them, I would like the definition to be system-wide.

You have a timestamp in your database (non-varchar). You have a "timestamp" in your Java (non-String, probably a Date or Calendar). The difference is in the String/textual representation that the client is providing to you, be it psql, be it "toString()" method of your Java object. If you used a different database client, I bet the output would be a third variation :-)

My point is: you should not depend on the String representation of dates (or any other non-String object, for that matter). If any of your code depends on that, I'd say it's wrong. Period.

And if you need to touch more than one file to change how a date is represented as text for your end-user, then I'd say that you have an opportunity for change there: externalize the date format and free your code from "fixed" configuration.

In short: use splash's approach.

answered Feb 28, 2011 at 16:43

jpkroehling

14.1k1 gold badge40 silver badges40 bronze badges

4 Comments

Dikla Over a year ago

I thought about changing the mapping to include the format, so I won't rely on any default representation. Is there such option?

jpkroehling Over a year ago

The correct representation of a timestamp in Java is a Date or Calendar object. Mapping a String representation for a database timestamp is suboptimal. But if you indeed need this, you can create your UserType and tell Hibernate to use it instead of the default type which handles timestamps.

Dikla Over a year ago

How do I do that? Do you have a link or some other information? Thanks!

jpkroehling Over a year ago

Look at the code from the test suite, here: github.com/hibernate/hibernate-core/tree/master/hibernate-core/… . Or, if you prefer, you can look at the actual implementation for the Timestamp type: github.com/hibernate/hibernate-core/blob/master/hibernate-core/…

Frank Heikens · Accepted Answer · 2011-02-28 14:16:41Z

0

Hibernate doesn't have a datatype timestamp with time zone, it has timestamp and timezone but that takes two fields, not just one field.

http://www.java2s.com/Code/Java/Hibernate/JavaTypeVSHibernateType.htm

answered Feb 28, 2011 at 14:16

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

Dikla Over a year ago

The mapping is to Timestamp. I don't care about the timezone here. Thanks.

Collectives™ on Stack Overflow

PostgreSQL and Hibernate: How do I format timestamp output?

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related