Re: Re: [JDBC] Timestamp parameter gets sent as date

<p>Updated with Types.TIMESTAMP. Did not help. Though, I did not expect it to, since it&#39;s different algorithm branch.</p>

<p>&nbsp;</p>

<blockquote>
<div>
<div>&nbsp;
<div>
<div>
<div>
<div>On 22 September 2017 at 06:43, Genya <span dir="ltr">&lt;<a href="mailto:genyap(at)online(dot)ua" target="_blank">genyap(at)online(dot)ua</a>&gt;</span> wrote:</div>
</div>
</div>
</div>

<div>
<blockquote>
<p>I&#39;m facing quite weird situation working on pure JDBC Java code connected to Postgres DB.</p>

<p>The SQL used for PreparedStatement is relatively simple:</p>

<p><span style="font-family:&amp">SELECT * FROM test t INNER JOIN .... </span></p>

<p><span style="font-family:&amp">WHERE&nbsp; <a href="http://t.id" target="_blank">t.id</a> = ? AND (?::TIMESTAMP IS NULL OR t.date + t.time &lt;= ?::TIMESTAMP WITHOUT TIME ZONE) </span></p>

<p>&nbsp;</p>

<p><span style="font-family:&amp">t.date is [date NOT NULL]<br />
t.time is [time without time zone NOT NULL]</span></p>

<p>&nbsp;</p>

<p>So, Java code to prepare and set parameters is as following:</p>

<p><span style="font-family:&amp">PreparedStatement statement = connection.prepareStatement(<strong><em>SQL</em></strong>);</span>&nbsp;</p>
</blockquote>

<blockquote>
<p><span style="font-family:&amp">statement.setInt(<span style="color:rgb(0,0,255)">1</span>, id);</span></p>

<p><span style="font-family:&amp">if (date== null) {</span></p>
</blockquote>

<div>you are changing the type of the parameter here when you setNull Types.DATE. use Types.TIMESTAMP.&nbsp;</div>

<blockquote>&nbsp;</blockquote>

<div>&nbsp;</div>

<blockquote>
<p><span style="font-family:&amp">&nbsp;&nbsp; statement.setNull(<span style="color:rgb(0,0,205)">2</span>, Types.<em>DATE</em>);<br />
&nbsp;&nbsp; statement.setNull(<span style="color:rgb(0,0,205)">3</span>, Types.<em>DATE</em>);<br />
}</span></p>
</blockquote>

<blockquote>
<p><span style="font-family:&amp">else {<br />
&nbsp;&nbsp; statement.setTimestamp(<span style="color:rgb(0,0,205)">2</span>, Timestamp.<em>valueOf</em>(date));<br />
&nbsp;&nbsp; statement.setTimestamp(<span style="color:rgb(0,0,205)">3</span>, Timestamp.<em>valueOf</em>(date));<br />
}</span></p>

<p><span style="font-family:&amp">ResultSet rs = statement.executeQuery();</span></p>

<p>&nbsp;</p>

<p>The issue is: though Java code sets date parameters to PreparedStatement correctly (checked with logging) i.e. $1 = &#39;29&#39;, $2 = &#39;2017-01-14:13:00&#39;, $3 = &#39;2017-01-14:13:00&#39; , but actual query performed by PostgreSQL server (logging) <strong>SOMETIMES </strong>shows parameters used <span style="font-family:&amp">GMT DETAIL:&nbsp; parameters: $1 = &#39;29&#39;, $2 = &#39;2017-01-14&#39;, $3 = &#39;2017-01-14&#39;</span>.&nbsp; Strangest thing that in 95% cases of same request with different params the output (behaviour and parameters) is correct, i.e. parameters are set and seen as DATE + TIME both from API and PostgreSQL. Moreover, repeteadly passing problematic parameters finally gets right query&#39;s behavior and output, sometimes after few tries or Java application restart. Worth mentioning, Java app does not do any &quot;caching&quot; or data modification - it&#39;s simple REST API for DB calls.</p>

<p>I&#39;m not sure if it might be something very simple (JDBC/DB configuration) or a bug in postgres JDBC driver - since there is no other sofware to blame. Java code seemingly does everything right but PostgresSQL <strong>SOMETIMES </strong>receives different parameters (date instead of date + time).</p>

<p>I&#39;ve tried to enable postgres JDBC driver logging as it&#39;s said in documentation but did not manage it to operate with Spring Boot logging.</p>

<p>&nbsp;</p>
</blockquote>

<div>&nbsp;</div>

<div>
<div>
<div>
<div>Dave Cramer<br />
<br />
<a href="mailto:davec(at)postgresintl(dot)com" target="_blank">davec(at)postgresintl(dot)com</a></div>

<div><a href="http://www.postgresintl.com/" target="_blank">www.postgresintl.com</a></div>
</div>
</div>
</div>
&nbsp;

<div>&nbsp;</div>
</div>
</div>
</div>
</blockquote>

<p>&nbsp;</p>

<p>&nbsp;</p>