How to use Joda-Time with java.sql.Timestamp

Question

I have a prepared report

INSERT INTO mst(time) VALUES (?); 

where time is a timestamp type in PostgreSQL . I am inserting a Joda-Time DateTime object, or should I say I'm trying. I cannot find a way to convert a DateTime object to java.sql.Timestamp . I have read the Joda-Time docs and see no links to this.

Thank.

Answer 1

You can convert a Joda DateTime to a long date (milliseconds from an era) and then create a timestamp.

 DateTime dateTime = new DateTime(); Timestamp timeStamp = new Timestamp(dateTime.getMillis()); 

Answer 2

The JodaTime DateTime constructor can handle this for you now. (I'm not sure if this was true when the question was posted, but this is Google’s correct result, so I decided that I would add a new solution.)

There are several API options:

 public DateTime(Object instant); public DateTime(Object instant, DateTimeZone zone); 

Both options accept java.sql.Timestamp because it extends java.util.Date, but Nanoseconds will be ignored (halfway) because DateTime and Date only have a resolution in milliseconds *. Without a specific time zone, DateTimeZone.UTC will default.

<Didactic Mode>
"Resolution" is the number of digits. "Accuracy" is the exact accuracy of the presentation. For example, MSSQL DateTime has a millisecond resolution, but only ~ 1/3 of the second precision (DateTime2 has a variable resolution and higher accuracy).
</ Didactic mode>

Millisecond resolution UTC timestamp Example:

 new DateTime(resultSet.getTimestamp(1)); 

If you use TIMESTAMP WITH TIME ZONE in your database, you cannot use java.sql.Timestamp because it does not support time zones. You will need to use ResultSet # getString and parse the string.

Timestamp without a time zone with a second resolution example **:

 LocalDateTime dt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss") .parseLocalDateTime(resultSet.getString(1)); 

UTC timestamp with second resolution example **:

 DateTime dt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss") .parseDateTime(resultSet.getString(1)); 

Time stamp with time zone (offset format) with a second resolution example **:

 DateTime dt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss Z") .parseDateTime(resultSet.getString(1)); 

Bonus: DateTimeFormat # forPattern statically caches parsers by template, so you don't need to.

<Didactic Mode>
I usually recommend using String in your DBO model to make the resolution explicit and avoid generating intermediate objects. (Is 2013-11-14 09:55:25 equal to 2013-11-14 09: 55: 25.000?) I usually try to distinguish between "database model objects" that optimize data retention problems and "business model objects" that optimize to use the service level with the conversion / display level between them. I find that CRUD-based DAOs generating business objects directly tend to mix priorities and optimize for none, throwing exceptions from unexpected places due to missed-edge cases. Having an explicit transformation layer also allows you to add confirmation if necessary, for example, if you do not control the data source. Separation of problems also makes it easier to test each layer independently.
</ Didactic mode>

* If you need to solve nanosecond resolution in your business model, you will have to use a different library.

** Timeline String format may differ from the database, but not sure.