Databases
StackOverflow2013
Postsdbo
POTimezones in SQL DATE vs java.sql.Date
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTimezones in SQL DATE vs java.sql.Date
    text
    copied!<p>I'm getting a bit confused by the behaviour of the SQL DATE data type vs. that of <code>java.sql.Date</code>. Take the following statement, for example:</p> <pre class="lang-sql prettyprint-override"><code>select cast(? as date) -- in most databases select cast(? as date) from dual -- in Oracle </code></pre> <p>Let's prepare and execute the statement with Java</p> <pre class="lang-java prettyprint-override"><code>PreparedStatement stmt = connection.prepareStatement(sql); stmt.setDate(1, new java.sql.Date(0)); // GMT 1970-01-01 00:00:00 ResultSet rs = stmt.executeQuery(); rs.next(); // I live in Zurich, which is CET, not GMT. So the following prints -3600000, // which is CET 1970-01-01 00:00:00 // ... or GMT 1969-12-31 23:00:00 System.out.println(rs.getDate(1).getTime()); </code></pre> <p>In other words, the GMT timestamp I bind to the statement becomes the CET timestamp I get back. At what step is the timezone added and why?</p> <p>Note:</p> <ul> <li><p>I have observed this to be true for any of these databases: </p> <p>DB2, Derby, H2, HSQLDB, Ingres, MySQL, Oracle, Postgres, SQL Server, Sybase ASE, Sybase SQL Anywhere</p></li> <li>I have observed this to be false for SQLite (which doesn't really have true <code>DATE</code> data types)</li> <li>All of this is irrelevant when using <code>java.sql.Timestamp</code> instead of <code>java.sql.Date</code></li> <li>This is a similar question, which doesn't answer this question, however: <a href="https://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date">java.util.Date vs java.sql.Date</a></li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload