Professional Web Applications Themes

SQL Timestamp comparisons - Java issues - IBM DB2

Hello, I am currently trying to execute SQL via JDBC to a DB2 database. I am trying to find a specific record(s) that match a specific date and time. The column in the database is of Timestamp type and is populated by a host of other processes in many different languages. My problem is that Java's Calendar object does not store nanoseconds and therefore is not as precise as the data that is in my target column in the database. So when I search for a record whose Timestamp value is in the database as '2003-09-17-12.25.36.126002' the SQL that results ...

  1. #1

    Default SQL Timestamp comparisons - Java issues

    Hello, I am currently trying to execute SQL via JDBC to a DB2
    database. I am trying to find a specific record(s) that match a
    specific date and time. The column in the database is of Timestamp
    type and is populated by a host of other processes in many different
    languages. My problem is that Java's Calendar object does not store
    nanoseconds and therefore is not as precise as the data that is in my
    target column in the database. So when I search for a record whose
    Timestamp value is in the database as '2003-09-17-12.25.36.126002' the
    SQL that results looks for the Timestamp value of
    '2003-09-17-12.25.36.126000'. What I need is to be able to get my SQL
    statement to match on all but the last three digits.

    I realize the the Java's Timestamp class does handle down to the
    nanoseconds and would allow me to get an exact match. However, due to
    the programming standards imposed on the project, we are not allowed
    to use the Java's Timestamp class other than at the lowest data access
    level - all other objects must use Calendar. So on my project, the
    data that is taken from the user is first stored as a Calendar and
    then is converted to a Timestamp when it hits our JDBC adaptor code.

    In a nutshell - How would I code my where clause and what SQL would be
    the most efficient in allowing me to compare all but the last three
    digits of a Timestamp field?

    Thanks,
    Ben Stucky
    Ben Guest

  2. #2

    Default Re: SQL Timestamp comparisons - Java issues

    "Ben" <benstuckyhotmail.com> wrote in message
    news:edc29e7f.0309182233.6b8b443posting.google.co m...
    > Hello, I am currently trying to execute SQL via JDBC to a DB2
    > database. I am trying to find a specific record(s) that match a
    > specific date and time. The column in the database is of Timestamp
    > type and is populated by a host of other processes in many different
    > languages. My problem is that Java's Calendar object does not store
    > nanoseconds and therefore is not as precise as the data that is in my
    > target column in the database. So when I search for a record whose
    > Timestamp value is in the database as '2003-09-17-12.25.36.126002' the
    > SQL that results looks for the Timestamp value of
    > '2003-09-17-12.25.36.126000'. What I need is to be able to get my SQL
    > statement to match on all but the last three digits.
    >
    > I realize the the Java's Timestamp class does handle down to the
    > nanoseconds and would allow me to get an exact match. However, due to
    > the programming standards imposed on the project, we are not allowed
    > to use the Java's Timestamp class other than at the lowest data access
    > level - all other objects must use Calendar. So on my project, the
    > data that is taken from the user is first stored as a Calendar and
    > then is converted to a Timestamp when it hits our JDBC adaptor code.
    >
    > In a nutshell - How would I code my where clause and what SQL would be
    > the most efficient in allowing me to compare all but the last three
    > digits of a Timestamp field?
    >
    > Thanks,
    > Ben Stucky
    How about:

    between '2003-09-17-12.25.36.126000' and '2003-09-17-12.25.36.126999'


    Mark A Guest

Similar Threads

  1. AS to Java Translation Issues
    By the123monster in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: October 6th, 01:45 AM
  2. int4 -> unix timestamp -> sql timestamp; abstime?
    By Roman Neuhauser in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 12th, 01:02 AM
  3. Date comparisons using UNIX timestamp?
    By sue in forum PHP Development
    Replies: 4
    Last Post: February 17th, 10:45 PM
  4. chaining comparisons
    By Kurt M. Dresner in forum Ruby
    Replies: 28
    Last Post: July 22nd, 05:03 AM
  5. Date comparisons
    By melo in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 11:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139