66

I Have a prepared statement

INSERT INTO mst(time) VALUES (?);

where time is of type Timestamp in a PostgreSQL database.
I am inserting a Joda-Time DateTime object, or I should say I am trying to. I can find no way to convert the DateTime object into a java.sql.Timestamp. I have read the Joda-Time docs and see no reference to this.

Thanks.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
WolfmanDragon
  • 7,851
  • 14
  • 49
  • 61

2 Answers2

90

You can convert a Joda DateTime to a long (millis since the epoch) first, and then create a Timestamp from that.

DateTime dateTime = new DateTime();
Timestamp timeStamp = new Timestamp(dateTime.getMillis());
Jack Leow
  • 21,945
  • 4
  • 50
  • 55
  • 8
    Where about the TimeZone component ? You have only "copied" the date and time but not the timezone which can affect the actual value... – mP. Jul 09 '10 at 04:14
  • 6
    Could you elaborate on what you mean? dateTime.getMillis() returns the milliseconds since epoch, which takes timezone into account. – Jack Leow Jul 09 '10 at 23:11
  • 6
    It's worth noting that joda-time does not store nanoseconds whereas Timestamp does. Any conversion between the two will lose nanosecond precision. – Gili Jun 20 '11 at 15:12
  • Any solution which preserves the original time zone value? – Jan Nov 24 '11 at 11:10
  • As I asked before, could you clarify what you mean? – Jack Leow Nov 24 '11 at 19:06
  • 1
    @Jack Leow What he means is that if the DateTime has a time zone that makes the date change day at midnight, then using a timestamp that looses the time zone gets the date go back in time one day. And that is how you have DAO unit tests that fail only around midnight :-) – Stephane Dec 04 '11 at 23:28
  • Yeah, if you live in New Zealand, this can lead to software that fails reliably every morning, and works correctly every afternoon. I speak from personal experience, when I was working mostly afternoons, and I was paired with a tester who worked only mornings. – Dawood ibn Kareem Aug 09 '12 at 23:37
  • There is no way to specify a time zone for JDBC date/time types, even when the target database supports it. This is because all types are derived from java.util.Date which is "broken" in this regard. If you need time zone information, I suggest to add a second column to the database which contains the TZ and save all points in time as if they were in UTC. Alternatively, you can use native SQL syntax but then, you will have to convert types yourself to what the native SQL expects. – Aaron Digulla Jan 22 '13 at 15:23
  • 20
    For posterity, if you need to convert from a Timestamp to a Joda DateTime, `new DateTime(timeStamp.getTime())`. – Dave Foster Jul 17 '13 at 21:23
  • 1
    Note that if your DateTime is running in a different timezone (say UTC) then your new Timestamp will be reset to your own timezone, which can yield bugs. – Mark Gjøl May 19 '14 at 12:39
  • @DaveFoster We can drop the call to `getTime` in your suggestion in Joda-Time version 2.3. Merely pass the java.sql.Timestamp object to the DateTime constructor, like this: `new DateTime( timeStamp )`, though a better practice would specify a time zone like this: `new DateTime( timeStamp, DateTimeZone.UTC )` See the [sibling answer by KarlKFI](http://stackoverflow.com/a/19986708/642706). – Basil Bourque Jul 05 '14 at 22:17
  • BEWARE. `new Timestamp()` internally uses java `Calendar`, which is initialized with local env timezone. So when you add this timestamp to a query to server, it will send year/month/day/etc, not nanos. So if you store your timestamps in UTC on the server (like many people do, and MySQL can only store in UTC), then this code won't work correctly -- the TIMESTAMP sent to server will denote different instant (different milliseconds) than was in your Joda DateTime. – Dzmitry Lazerka Dec 26 '16 at 23:17
10

JodaTime's DateTime constructor can handle this for you now. (I'm not sure if that was true when the question was posted, but it's a top Google result so I figured I'd add a newer solution.)

There's a few 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 the Nanoseconds will be ignored (floored), because DateTime and Date only have millisecond resolution*. Without a specific timezone it will default to DateTimeZone.UTC.

<Didactic Mode>
"Resolution" is how many digits are provided. "Precision" is how accurate the representation is. For example, MSSQL's DateTime has millisecond resolution, but only ~1/3 of a second precision (DateTime2 has variable resolution and higher precision).
</Didactic Mode>

UTC Timestamp with Millisecond Resolution Example:

new DateTime(resultSet.getTimestamp(1));

If you're using TIMESTAMP WITH TIME ZONE in your database then you can't use java.sql.Timestamp, because it doesn't support time zones. You'll have to use ResultSet#getString and parse the string.

Timestamp without Time Zone with 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));

Timestamp with Time Zone (offset format) with 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 pattern so you don't have to.

<Didactic Mode>
I generally recommend using a String in your DBO model in order to make resolution explicit and avoid generating intermediate objects. (Is 2013-11-14 09:55:25 equal to 2013-11-14 09:55:25.000?) I generally try to distinguish between "database model objects" optimizing for data preservation concerns and "business model objects" optimizing for service level usage with a transformation/mapping layer in between. I find having CRUD based DAOs generating business objects directly tends to mix up the priorities and optimize for neither, throwing exceptions from unexpected places because of missed edge cases. Having an explicit transformation layer also allows you to add validation if necessary, like if you don't control the data source. Separating the concerns also makes it easier to test each layer independently.
</Didactic Mode>

* If you need to resolve to nanosecond resolution in your business model you'll have to use a different library.

** Timestamp String format may vary between databases, not sure.

facundofarias
  • 2,973
  • 28
  • 27
KarlKFI
  • 3,102
  • 1
  • 15
  • 15
  • Regarding nanoseconds… FYI, [JSR 310: Date and Time API](http://jcp.org/en/jsr/detail?id=310) being built into Java 8, and Joda-Time's successor, does indeed support nanosecond resolution. Keep in mind that many computers’ clock do not keep time to that granularity. Aside: interesting note from the JSR spec: *These classes use nanosecond precision. The classes have sufficient accuracy to represent any nanosecond instant within the current age of the universe.* – Basil Bourque Nov 20 '13 at 07:55
  • I wish they would correct the diction in that spec... Java has no control over the hardware's precision, and accuracy is a function of precision and correctness (or 'trueness'). The classes have nanosecond RESOLUTION and enough size in memory to represent a very large number of nanoseconds. – KarlKFI Nov 20 '13 at 17:01
  • As for your assertion about granularity: some systems don't have nanosecond resolution, and some don't have nanosecond presision or correctness. System.nanoTime() probably adds zeros on any system where the operating system does not provide nanosecond resolution, but that only increases resolution, not precision, correctness or accuracy. Here's the most thorough source I can find on the terminology: http://www.tutelman.com/golf/measure/precision.php – KarlKFI Nov 20 '13 at 17:02
  • 2
    I don’t understand your statement, `If you're using TIMESTAMP WITH TIME ZONE in your database then you can't use java.sql.Timestamp`. Despite its misleading name, the type `TIMESTAMP WITH TIME ZONE` does not actually store any time zone information. That type considers time zone when inserting and retrieving a date-time value but always stores the value in UTC. So we can retrieve a `TIMESTAMP WITH TIME ZONE` value as a JDBC type `java.sql.Timestamp` object to feed to constructor of a `org.joda.time.DateTime` instance. I tested using the `now()` function. – Basil Bourque Jul 05 '14 at 22:33
  • FYI: The [Joda-Time](http://www.joda.org/joda-time/) project, now in [maintenance mode](https://en.wikipedia.org/wiki/Maintenance_mode), advises migration to the [java.time](http://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html) classes. – Basil Bourque Dec 11 '16 at 01:51