Timestamps are logged incorrectedly

[cowwoc]

If a query has a Timestamp parameter then com.p6spy.engine.common.Value.convertToString() treats it as a Date and truncates the time component.

I wasted hours trying to fix a bug in my code that did not actually exist because the logs mislead me. Please add special handling for Timestamp in convertToString()

Alternatively, consider changing the default value of databaseDialectDateFormat from dd-MMM-yy to yyyy-MM-dd'T'HH:mm:ss.SSSZ which I believe is equivalent to ISO-8601.

[typekpb]

PR including the now failing test and the fix for it would be much appreceated.

…

On Feb 17, 2018 13:27, "Gili Tzabari" ***@***.***> wrote: If a query has a Timestamp parameter then com.p6spy.engine.common.Value. convertToString() treats it as a Date and truncates the time component. I wasted hours trying to fix a bug in my code that did not actually exist because the logs mislead me. Please add special handling for Timestamp in convertToString() — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#434>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAegbOM3u56S0K2Evk-iUjDrohyOU6ucks5tVsW8gaJpZM4SJS0n> .

[cowwoc]

I don't see a failing test.

[typekpb]

I mean, you should write one. As all our current tests are green. So the best way would be to commit the failing test first, to show the evidence of the problem and afterwards commit the fix in the same PR, to show us your solution.

…

On Feb 19, 2018 00:03, "Gili Tzabari" ***@***.***> wrote: I don't see a failing test. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#434 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAegbHu6vTjKol4E7NsX8I6wnQA3R6Tkks5tWKxTgaJpZM4SJS0n> .

[cowwoc]

Fixing the default is not too difficult but I am not familiar with gradle or your particular unit test structure. Can you please point me to another unit test that verifies the output for dates so I can use it as a basis for my new Timestamp test?

[typekpb]

sample test: com.p6spy.engine.spy.LoggedSQLValidTest.testPreparedStatementExecUpdate()

[andrei-ivanov]

Bitten by the same problem:
I was reading http://in.relation.to/2018/02/20/java8-date-time-mapping/ and I initially had only p6spy enabled and it showed weird queries:

select jpaauditlo0_.id as id1_0_, jpaauditlo0_.activity as activity2_0_, jpaauditlo0_.context as context3_0_, jpaauditlo0_.parameters as paramete4_0_, jpaauditlo0_.thread_id as thread_i5_0_, jpaauditlo0_.thread_name as thread_n6_0_, jpaauditlo0_.timestamp as timestam7_0_, jpaauditlo0_.user_name as user_nam8_0_ from audit_entities jpaauditlo0_ where lower(jpaauditlo0_.activity)=lower('transactionTestEvent2') and lower(jpaauditlo0_.user_name)=lower('Fred') and jpaauditlo0_.timestamp>='07-Sep-18' and jpaauditlo0_.timestamp<='07-Sep-18'

After enabling Hibernate logging I see this:

17:03:59.030 [jdk-http-server-0] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [transactionTestEvent2]
17:03:59.030 [jdk-http-server-0] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [Fred]
17:03:59.030 [jdk-http-server-0] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [TIMESTAMP] - [2018-09-07T09:03:58.719Z]
17:03:59.030 [jdk-http-server-0] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2018-09-07T19:03:58.719Z]

[typekpb]

Could it be that you should set the property: databaseDialectDateFormat ?

As our docs (https://p6spy.readthedocs.io/en/latest/configandusage.html#common-property-file-settings) say:

# format that is used for logging of the date/time/... (has to be compatible with java.text.SimpleDateFormat)
# (default is dd-MMM-yy)
#databaseDialectDateFormat=dd-MMM-yy

could you try to set it like:

databaseDialectDateFormat=yyyy-MM-dd'T'HH:mm:ss.SSSX

(just guessing from: https://stackoverflow.com/questions/2201925/converting-iso-8601-compliant-string-to-java-util-date), if that works, feel free to report back, so that we can add if to documentation. Or possibly even change the defaults.

[andrei-ivanov]

Hmm, indeed that works.
But that means that I have misunderstood what p6spy provides.
I thought I would actually get logged the SQL string that the driver sends to the DB, as that would help me understand if I did something wrong when configuring my JPA mappings or queries and if there are any type mismatches.

[typekpb]

well, to p6spy it comes as an object, so no idea how was it originally formatted (that is my understanding).

@andrei-ivanov @cowwoc do you request any change in the default value here? If not. I'd close it, at it sounds just like a question that required clarification.

[andrei-ivanov]

Well, as long as you say that there's no way to tell what exactly is the form that the database will get that value, I guess you can close it, at least from my side.

[cowwoc]

@typekpb Per my original comment, I am requesting the default value be changed to yyyy-MM-dd'T'HH:mm:ss.SSSZ

[typekpb]

@quintonm @felixbarny any objections to change the default format? If not I'd go for it.

[felixbarny]

I'm fine with it