StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest way to get SQL Server date data into Joda object?
text
Body
copied!<p>TL;DR version: Please confirm (or if not, provide assistance) that I'm getting SQL Server <code>datetimeoffset</code> data into my Joda Time Java objects correctly.</p> <hr> <p>I'm in the middle of planning moving our database and Java code to be time-zone-aware. To accomplish this, I have been all over <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices">this post</a> and am attempting to implement the best practices. Note that all code is considered "throw-away" code, so I'm not really concerned with efficiency here; just correctness.</p> <p>Our environment consists of a Microsoft SQL Server 2008 database and a Java service layer whereby we access all data through stored procedures and Spring <code>SimpleJdbcCall</code>'s.</p> <p>One of the best practices mentioned is to use the Joda Time library. Since this is new to me, as is the <code>datetimeoffset</code> SQL datatype, I'd like to ensure that I'm doing this correctly (and thus not losing any information.)</p> <p>Inside SQL Server, I created a table for testing all of the various SQL Server get-time-type functions:</p> <pre><code>CREATE TABLE MIKE_TEMP ( ID INT NOT NULL IDENTITY, BLAH NVARCHAR(255), DT_GET_DATE DATETIME DEFAULT GETDATE() NOT NULL, DT_GET_UTC_DATE DATETIME DEFAULT GETUTCDATE() NOT NULL, DT_SYS_DATE_TIME DATETIME DEFAULT sysdatetime() NOT NULL, DT_SYS_UTC_DATE_TIME DATETIME DEFAULT sysutcdatetime() NOT NULL, DT_SYS_DATE_TIME_OFFSET DATETIME DEFAULT sysdatetimeoffset() NOT NULL, DTO_GET_DATE DATETIMEOFFSET DEFAULT GETDATE() NOT NULL, DTO_GET_UTC_DATE DATETIMEOFFSET DEFAULT GETUTCDATE() NOT NULL, DTO_SYS_DATE_TIME DATETIMEOFFSET DEFAULT sysdatetime() NOT NULL, DTO_SYS_UTC_DATE_TIME DATETIMEOFFSET DEFAULT sysutcdatetime() NOT NULL, DTO_SYS_DATE_TIME_OFFSET DATETIMEOFFSET DEFAULT sysdatetimeoffset() NOT NULL ); </code></pre> <p>Into this table, I added one value, <code>blah = 'Hello World!'</code>. The resulting data is:</p> <pre><code>ID BLAH DT_GET_DATE DT_GET_UTC_DATE DT_SYS_DATE_TIME DT_SYS_UTC_DATE_TIME DT_SYS_DATE_TIME_OFFSET DTO_GET_DATE DTO_GET_UTC_DATE DTO_SYS_DATE_TIME DTO_SYS_UTC_DATE_TIME DTO_SYS_DATE_TIME_OFFSET -- ------------ ------------------- ------------------- ------------------- -------------------- ----------------------- ---------------------------------- ---------------------------------- ---------------------------------- ---------------------------------- ---------------------------------- 1 Hello World! 2012-02-15 08:58:41 2012-02-15 14:58:41 2012-02-15 08:58:41 2012-02-15 14:58:41 2012-02-15 08:58:41 2012-02-15 08:58:41.6000000 +00:00 2012-02-15 14:58:41.6000000 +00:00 2012-02-15 08:58:41.6005458 +00:00 2012-02-15 14:58:41.6005458 +00:00 2012-02-15 08:58:41.6005458 -06:00 </code></pre> <p>There is a corresponding stored procedure that simply does a <code>select * from MIKE_TEMP</code> and returns all data as output parameters.</p> <p>The Java code that accesses this data is (only "interesting" imports included for clarity):</p> <pre><code>import org.joda.time.DateTime; import java.util.Date; @Component public class MikeTempDaoImpl { private static final Logger logger = LoggerFactory.getLogger(MikeTempDaoImpl.class); private DataSource dataSource; @Autowired public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } public DataSource getDataSource() { return dataSource; } public MikeVTemp getMikeTemp() { SimpleJdbcCall data = new SimpleJdbcCall(getDataSource()); data.withProcedureName("get_MIKE_TEMP"); data.withoutProcedureColumnMetaDataAccess(); data.declareParameters( new SqlOutParameter("ID", Types.INTEGER), new SqlOutParameter("BLAH", Types.NVARCHAR), new SqlOutParameter("DT_GET_DATE", Types.TIMESTAMP), new SqlOutParameter("DT_GET_UTC_DATE", Types.TIMESTAMP), new SqlOutParameter("DT_SYS_DATE_TIME", Types.TIMESTAMP), new SqlOutParameter("DT_SYS_UTC_DATE_TIME", Types.TIMESTAMP), new SqlOutParameter("DT_SYS_DATE_TIME_OFFSET", Types.TIMESTAMP), new SqlOutParameter("DTO_GET_DATE", Types.TIMESTAMP), new SqlOutParameter("DTO_GET_UTC_DATE", Types.TIMESTAMP), new SqlOutParameter("DTO_SYS_DATE_TIME", Types.TIMESTAMP), new SqlOutParameter("DTO_SYS_UTC_DATE_TIME", Types.TIMESTAMP), new SqlOutParameter("DTO_SYS_DATE_TIME_OFFSET", Types.TIMESTAMP) ); Map out; try { out = data.execute(); } catch (Exception ex) { logger.error(ex.getMessage()); } int id = (Integer) out.get("ID"); String blah = (String) out.get("BLAH"); DateTime dtGetDate = new DateTime((Date) out.get("DT_GET_DATE")); DateTime dtGetUtcDate = new DateTime((Date) out.get("DT_GET_UTC_DATE")); DateTime dtSysDateTime = new DateTime((Date) out.get("DT_SYS_DATE_TIME")); DateTime dtSysUtcDateTime = new DateTime((Date) out.get("DT_SYS_UTC_DATE_TIME")); DateTime dtSysDateTimeOffset = new DateTime((Date) out.get("DT_SYS_DATE_TIME_OFFSET")); DateTime dtoGetDate = new DateTime((Date) out.get("DTO_GET_DATE")); DateTime dtoGetUtcDate = new DateTime((Date) out.get("DTO_GET_UTC_DATE")); DateTime dtoSysDateTime = new DateTime((Date) out.get("DTO_SYS_DATE_TIME")); DateTime dtoSysUtcDateTime = new DateTime((Date) out.get("DTO_SYS_UTC_DATE_TIME")); DateTime dtoSysDateTimeOffset = new DateTime((Date) out.get("DTO_SYS_DATE_TIME_OFFSET")); MikeTemp mt = new MikeTemp.Builder() .id(id) .blah(blah) .dtGetDate(dtGetDate) .dtGetUtcDate(dtGetUtcDate) .dtSysDateTime(dtSysDateTime) .dtSysUtcDateTime(dtSysUtcDateTime) .dtSysDateTimeOffset(dtSysDateTimeOffset) .dtoGetDate(dtoGetDate) .dtoGetUtcDate(dtoGetUtcDate) .dtoSysDateTime(dtoSysDateTime) .dtoSysUtcDateTime(dtoSysUtcDateTime) .dtoSysDateTimeOffset(dtoSysDateTimeOffset) .build(); System.out.println("id = [" + mt.getId() + "]"); System.out.println("blah = [" + mt.getBlah() + "]"); System.out.println("dtGetDate = [" + mt.getDtGetDate() + "]"); System.out.println("dtGetUtcDate = [" + mt.getDtGetUtcDate() + "]"); System.out.println("dtSysDateTime = [" + mt.getDtSysDateTime() + "]"); System.out.println("dtSysUtcDateTime = [" + mt.getDtSysUtcDateTime() + "]"); System.out.println("dtSysDateTimeOffset = [" + mt.getDtSysDateTimeOffset() + "]"); System.out.println("dtoGetDate = [" + mt.getDtoGetDate() + "]"); System.out.println("dtoGetUtcDate = [" + mt.getDtoGetUtcDate() + "]"); System.out.println("dtoSysDateTime = [" + mt.getDtoSysDateTime() + "]"); System.out.println("dtoSysUtcDateTime = [" + mt.getDtoSysUtcDateTime() + "]"); System.out.println("dtoSysDateTimeOffset = [" + mt.getDtoSysDateTimeOffset() + "]"); return mvt; } } </code></pre> <p>This is being exercised by a JUnit test:</p> <pre><code>@Test public void testDateData() throws Exception { MikeTemp mt = dao.getMikeTemp(); assertNotNull("MT should not be null, but it is.", mt); assertEquals(1, mt.getId()); assertEquals("Hello World!", mt.getBlah()); } </code></pre> <p>And the results from all of the println's are:</p> <pre><code>id = [1] blah = [Hello World!] dtGetDate = [2012-02-15T08:58:41.577-06:00] dtGetUtcDate = [2012-02-15T14:58:41.577-06:00] dtSysDateTime = [2012-02-15T08:58:41.580-06:00] dtSysUtcDateTime = [2012-02-15T14:58:41.600-06:00] dtSysDateTimeOffset = [2012-02-15T08:58:41.600-06:00] dtoGetDate = [2012-02-15T08:58:41.600-06:00] dtoGetUtcDate = [2012-02-15T14:58:41.600-06:00] dtoSysDateTime = [2012-02-15T08:58:41.600-06:00] dtoSysUtcDateTime = [2012-02-15T14:58:41.600-06:00] dtoSysDateTimeOffset = [2012-02-15T08:58:41.600-06:00] </code></pre> <p>Being as this server is in the US Central Time Zone, I definitely expect to see -06:00 for <em>some</em> of the results, but rather definitely not <em>all</em> of them. Have I missed something somewhere along the way? Is calling the Joda <code>DateTime(Object)</code> ctor with a <code>java.util.Date</code> object the correct thing to do in this situation? What else could/should I be doing that I'm not?</p> <p>Thanks!</p>

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