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]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[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.
