Also bitten by java.sql.Timestamp comparisons to :db.type/instant (d/with)

[terjesb]

OK, I was also just bitten by java.sql.Timestamp comparisons to
:db.type/instant (java.util.Date).


After writing this, I found a similar post in
https://groups.google.com/d/topic/datomic/_yZXAmzUPC4/discussion. So I
solved this by coercing the returned data to java.util.Date before shipping
to Datomic. Should this perhaps be a separate bullet in
http://docs.datomic.com/best-practices.html? That being said, I'm not sure
I understand why #4 below isn't true!


Background: I'm replicating data from MySQL to Datomic. I'm using
datomic.api/with to detect changed data, and skip unchanged records to
avoid creating "empty transactions". If returned tx-data has a length of 1,
only an empty transaction would be created.



I noticed that any timestamps returned from MySQL were always considered
changed when comparing java.sql.Timestamp against :db.type/instant.


This seems to be caused by (using UTC):


#inst "2013-08-22T00:00:00.000000000-00:00"

;; #inst "2013-08-22T00:00:00.000-00:00"


(= #inst "2013-08-22T00:00:00.000000000-00:00" #inst
"2013-08-22T00:00:00.000-00:00")

;; true


(java.sql.Timestamp. 113 7 22 0 0 0 0)

;; #inst "2013-08-22T00:00:00.000000000-00:00"


(= (java.sql.Timestamp. 113 7 22 0 0 0 0) #inst
"2013-08-22T00:00:00.000-00:00")

;; false!


(= (java.util.Date. (.getTime (java.sql.Timestamp. 113 7 22 0 0 0 0)))
#inst "2013-08-22T00:00:00.000-00:00")

;: true


(= (java.util.Date/from (.toInstant (java.sql.Timestamp. 113 7 22 0 0 0
0))) #inst "2013-08-22T00:00:00.000-00:00")

;; true, same as above

[Bill Kennedy]

I poked around a bit and found this:
http://stackoverflow.com/questions/8929242/compare-date-object-with-a-timestamp-in-java

So, it's a sticky situation in Java in general (comparing timestamps and
dates).

I will attest it is an easy trap to stumble into.

[Ben Kamphaus]

As the SO issue Bill points to mentions, the source of this issue is
outside the scope of Datomic's behavior as it has to do with documented
behavior of the Timestamp, not any of Datomic's time representations. See
the Timestamp docs:

https://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html

"The Timestamp.equals(Object) method never returns true when passed an
object that isn't an instance of java.sql.Timestamp, because the nanos
component of a date is unknown. As a result, the Timestamp.equals(Object) method
is not symmetric with respect to the java.util.Date.equals(Object) method."


Best,
Ben

Post by terjesb
OK, I was also just bitten by java.sql.Timestamp comparisons to
:db.type/instant (java.util.Date).
After writing this, I found a similar post in
https://groups.google.com/d/topic/datomic/_yZXAmzUPC4/discussion. So I
solved this by coercing the returned data to java.util.Date before shipping
to Datomic. Should this perhaps be a separate bullet in
http://docs.datomic.com/best-practices.html? That being said, I'm not
sure I understand why #4 below isn't true!
Background: I'm replicating data from MySQL to Datomic. I'm using
datomic.api/with to detect changed data, and skip unchanged records to
avoid creating "empty transactions". If returned tx-data has a length of 1,
only an empty transaction would be created.
I noticed that any timestamps returned from MySQL were always considered
changed when comparing java.sql.Timestamp against :db.type/instant.
#inst "2013-08-22T00:00:00.000000000-00:00"
;; #inst "2013-08-22T00:00:00.000-00:00"
(= #inst "2013-08-22T00:00:00.000000000-00:00" #inst
"2013-08-22T00:00:00.000-00:00")
;; true
(java.sql.Timestamp. 113 7 22 0 0 0 0)
;; #inst "2013-08-22T00:00:00.000000000-00:00"
(= (java.sql.Timestamp. 113 7 22 0 0 0 0) #inst
"2013-08-22T00:00:00.000-00:00")
;; false!
(= (java.util.Date. (.getTime (java.sql.Timestamp. 113 7 22 0 0 0 0)))
#inst "2013-08-22T00:00:00.000-00:00")
;: true
(= (java.util.Date/from (.toInstant (java.sql.Timestamp. 113 7 22 0 0 0
0))) #inst "2013-08-22T00:00:00.000-00:00")
;; true, same as above