5

I'm querying a Postgres database and looking for the best way to store a Postgres "timestamp with timezone" information.

the format is "yyyy-MM-dd' 'HH:mm:ss.SSSzz" (i.e. "2014-04-22 05:39:49.916+03")

i'd like to use a timestamp oriented type/class to keep the info (not String)

the following throws Unparsable date for all the TIME_FORMATS i could think of:

final String TIME_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSz";
final SimpleDateFormat sdf = new SimpleDateFormat(TIME_FORMAT);
final java.util.Date utilDate = sdf.parse("2014-04-22 05:39:49.916+03");
Improve this question
3
  • in the end i used java.sql.Timestamp and it worked perfectly. instead of reading the DB content as string and then building a date like above (with @Codebender 's fix - which worked perfectly), i was able to read the DB content (the timestamp) as java.sql.Timestamp. Commented Aug 25, 2015 at 14:50
  • FYI, that string format you showed is standard SQL date time format. The ISO 8601 standard defines similar formats but replaced the SPACE in the middle with a T. Commented Aug 25, 2015 at 17:28
  • Using java.sql.Timestamp is the right way currently. When you update to a JDBC 4.2 driver, you can dump the java.sql.Timestamp class to use new java.time types instead. Commented Aug 25, 2015 at 17:45

3 Answers 3

Reset to default
12

Your format is having ISO Timezone, so use X (not z).

final String TIME_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSX";

More Info here

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

1 Comment

In the end i used DateFormatUtils.ISO_DATETIME_TIME_ZONE_FORMAT.format(Date date); from org.apache.commons.lang3.time
2

java.sql.Timestamp

Current JDBC drivers will create. java.sql.Timestamp object for you when retrieving a value from either of Postgres’ time stamp types, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. 

java.sql.Timestamp ts = myResultSet.getTimestamp( 1 ) ;

No need for strings. Stick to using objects rather than strings for date-time values whenever possible.

java.time

In Java 8 and later you should be using the new java.time package. Avoid the old java.util.Date/.Calendar and so on.

When your JDBC driver is updated for JDBC 4.2, you will be able to retrieve your Postgres date-time value directly into the java.time types.

While waiting on such drivers, use the new conversion methods added to java.sql.Timestamp for going to and from an Instant.

Search StackOverflow for more info as this has been discussed many times already.

Improve this answer

3 Comments

Thanks for the explanation!, i'm using ibatis, and i'm not sure if it uses the JDBC drivers
@joeybaruch Do you mean MyBatis? IBatis is the predecessor, and has been retired. Both are frameworks built on top of JDBC. I'm concerned you are not aware of your JDBC driver. Behavior can vary between drivers as not every detail is spelled out in the JDBC spec. This is especially true for date-time work. You must learn the particular behavior of your driver with your database. Experiment with throwaway code to learn. Do not rely entirely on a framework like MyBatis.
i am using ibatis, it's proven to be enough for what i need.. and yes, i was not aware of my JDBC driver. could you elaborate on what you meant by "throwaway code", and why not to rely on MyBatis to extract sql data?
0

If you're using Java 8, you could store the data in a ZonedDateTime object, which stores a

date-time with a time-zone in the ISO-8601 calendar system, such as 2007-12-03T10:15:30+01:00 Europe/Paris.

Simple call toInstant() on the java.sql.Timestamp value in the result and you're good to go (thanks @Basil Bourque for pointing this out).

Prior to Java 8, you could use your existing solution. Note however that if your timezone info is stored as +03, you should have your time format pattern end with X, which is a ISO 8601 time zone.

Improve this answer

1 Comment

Using ZonedDateTime is the way to go, but no need for parsing. Go Postgres > JDBC driver > java.sql.Timestamp > toInstant method > ZonedDateTime.ofInstant( Instant , ZoneId ). No strings attached. :-)

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.