Hi, The discussion was moved to http://stackoverflow.com/questions/24377271/h2-database-how-can-you-do-a-point-query-with-a-timestamp-cannot-find-anythin/24379760#24379760
Please don't use StackOverflow AND the Google Group. Use one or the other. Regards, Thomas On Tue, Jun 24, 2014 at 8:09 AM, Thomas Mueller < [email protected]> wrote: > Hi, > > How do you format the output ("Results: name=...")? I guess you don't > print the nanoseconds part. Yes, you can't use SimpleDateFormat as it > doesn't support nanoseconds, but toString() and "cast(.. as varchar)" > should work, see below. What version of H2 do you use? > > Class.forName("org.h2.Driver"); > Connection conn = DriverManager.getConnection("jdbc:h2:mem:"); > PreparedStatement prep = conn.prepareStatement("select ?, cast(? as > varchar)"); > Timestamp ts = new Timestamp(1); > ts.setNanos(123456789); > prep.setTimestamp(1, ts); > prep.setTimestamp(2, ts); > ResultSet rs = prep.executeQuery(); > rs.next(); > System.out.println(rs.getString(1)); > System.out.println(rs.getString(2)); > System.out.println(ts); > > Regards, > Thomas > > > > On Monday, June 23, 2014, Kenton Garner <[email protected]> wrote: > >> How do you do a point query with a TIMESTAMP ? >> >> I have a table with a TIMESTAMP column. It is not the primary key, but >> for all practical purposes will be unique per row. >> The TIMESTAMP column may ( does not always ) contain nano seconds of >> precision. >> >> If I run the query... "SELECT NAME, MSG_TS FROM CDS_HISTORY WHERE MSG_TS >> < '2014-06-20 21:36:43' " I get... >> >> Results: name=[1687] msg_ts=[2014-06-20 21:36:42.485] >>> Results: name=[1686] msg_ts=[2014-06-20 21:36:42.47] >>> Results: name=[1685] msg_ts=[2014-06-20 21:36:42.455] >>> Results: name=[1684] msg_ts=[2014-06-20 21:36:42.441] >>> Results: name=[1683] msg_ts=[2014-06-20 21:36:42.426] >>> Results: name=[1682] msg_ts=[2014-06-20 21:36:42.412] >>> Results: name=[1681] msg_ts=[2014-06-20 21:36:42.397] >>> Results: name=[1680] msg_ts=[2014-06-20 21:36:42.381] >>> Results: name=[1679] msg_ts=[2014-06-20 21:36:42.366] >>> Results: name=[1678] msg_ts=[2014-06-20 21:36:42.35] >>> Results: name=[1677] msg_ts=[2014-06-20 21:36:42.335] >>> Results: name=[1676] msg_ts=[2014-06-20 21:36:42.32] >>> Results: name=[1675] msg_ts=[2014-06-20 21:36:42.306] >>> Results: name=[1674] msg_ts=[2014-06-20 21:36:42.289] >>> Results: name=[1673] msg_ts=[2014-06-20 21:36:42.268] >>> Results: name=[1672] msg_ts=[2014-06-20 21:36:42.247] >>> Results: name=[1671] msg_ts=[2014-06-20 21:36:42.147] >>> Results: name=[1670] msg_ts=[2014-06-20 21:36:42.127] >>> Results: name=[1669] msg_ts=[2014-06-20 21:36:42.111] >>> Results: name=[1668] msg_ts=[2014-06-20 21:36:42.057] >>> >> >> Using one row from the result set for a where clause... >> "SELECT NAME, MSG_TS FROM CDS_HISTORY WHERE MSG_TS = '2014-06-20 >> 21:36:42.111' " -> 0 rows... >> "SELECT NAME, MSG_TS FROM CDS_HISTORY WHERE MSG_TS = '2014-06-20 >> 21:36:42.111000' " -> 0 rows... >> "SELECT NAME, MSG_TS FROM CDS_HISTORY WHERE MSG_TS = '2014-06-20 >> 21:36:42.111000000' " -> 0 rows... >> >> The casting and format functions in H2 mention that >> java.text.SimpleDateFormat is used so I do not believe I can use them >> when nano seconds are involved. >> ( This example does not require nano seconds but I still have had no joy ) >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
