Announcement Announcement Module
Collapse
No announcement yet.
compare date with postgres Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • compare date with postgres

    Hello

    It may be not suitable to post here but if anyone know please help.

    To select all records between start and end dates, I have query:

    "SELECT * FROM time_table WHERE book_time BETWEEN ? AND ?");

    I have provide the start date as the first day of current month by using:

    Calendar cal = Calendar.getInstance();
    Date startDate= cal.set(Calendar.DATE, 1);

    The end date is the Current day (today) by using:

    Calendar cal = Calendar.getInstance();
    Date endDate = cal.getTime();

    However, this query cannot find the records that are added in the current day (today)

    Could you please help how to fix this problem
    Many thanks
    sho

  • #2
    As far as I know, BETWEEN is defined to exclude the border values. Though, this is implemented differently across databases. If I recall correctly, Oracle does include the border values.

    Anyway, this (only a bit more complicated) approach should be portable:
    Code:
    SELECT * from time_table WHERE book_time >= ? AND book_time <= ?
    Regards,
    Andreas

    Comment


    • #3
      Thanks

      Thanks
      However, I already tried that but it doesnot work

      Comment


      • #4
        Does it yield wrong results? What is the behavior if you perform a similar statement from a database client?

        Regards,
        Andreas

        Comment


        • #5
          Originally posted by Andreas Senft
          As far as I know, BETWEEN is defined to exclude the border values. Though, this is implemented differently across databases. If I recall correctly, Oracle does include the border values.
          Code:
          BETWEEN Operator
          
          The BETWEEN operator performs a Boolean test of a value against a range of values. It returns TRUE when the value is included in the range and FALSE when the value falls outside of the range. The results are NULL (unknown) if any of the range values are NULL.
          
          Rules at a Glance
          The BETWEEN operator is used to test an expression against a range of values. The BETWEEN operator may be used with any datatype except BLOB, CLOB, NCLOB, REF, and ARRAY.
          
          For example, we want to see title_ids that have year-to-date sales between 10,000 and 20,000:
           
          SELECT title_id
          FROM titles
          WHERE ytd_sales BETWEEN 10000 AND 20000
          
          BETWEEN is inclusive of the range of values listed. In this case, it includes the values of 10,000 and 20,000 in the search. If you want an exclusive search, then you must use the greater than (>) and less than (<) symbols:
          Re: O'Reilly SQL in a Nutshell By Kevin E. Kline 2006

          Both Oracle, and PostgreSQL, and MySQL, and DB2 and who else? are expected to support this semantics of the between operator.

          Comment


          • #6
            Thanks for the info. For comparison see here. Note the "IMPORTANT" section.

            So perhaps at least, there _might_ be differences (or have been, at least). Wouldn't be anything new in that area :-/

            Regards,
            Andreas

            Comment


            • #7
              Thanks all for your help

              Here is the details. I have a time_table with a field book_time

              CREATE TABLE time_table
              (
              ..
              book_ time timestamptz NOT NULL DEFAULT current_timestamp,
              ..
              )

              Then I insert some records into this table without book_time value. So the book_time got the default time: current_timestamp

              Then I using the query with start and end date in the previous post, no result is found. But in next day, I do it again, those records are shown.

              I donot know what happend here

              Regards

              Comment


              • #8
                If your database is running on a separate server, then there is the potential that the database server's date could differ from the application server's date. This date difference may be significant enough to explain the behavior you are describing.

                Ensure that the default current_timestamp is what you are expecting. We have dealt with this issue in my shop recently.

                Comment


                • #9
                  Hi
                  I run the application/database in local computer.
                  I fix the problem by using :

                  Calendar endDate = Calendar.getInstance();
                  //add one more day
                  endDate.add(Calendar.DATE, +1);

                  Comment


                  • #10
                    Maybe it would also suffice to set the time portion of the begin timestamp to 00:00:00:000 and the of the end timestamp to 23:59:59:999. That should cover the whole day and yields deterministic results.

                    Regards,
                    Andreas

                    Comment

                    Working...
                    X