11 Replies Latest reply: Feb 15, 2011 2:02 PM by 796440 RSS

    How to match time alone (without date) in a query

    sperkmandl
      Hi, I have a date colum named timeprop, where a time value has been inserted through:

      DateFormat.getTimeInstance(DateFormat.MEDIUM, "de_DE").parse("11:12:13")

      In SQL developer I can check the inserted value as:

      select timeprop from version_table where timeprop is not null;

      getting:

      TIMEPROP
      -------------------------
      01.01.1900 11:12:13

      so I guess Oracle added a default date as 1.01.1900. Ok.
      But if I run:

      select timeprop from version_table where timeprop = to_date('11:12:13', 'hh24:mi:ss');

      then I get no result. I succeed only after adding that default date to the query - by patching the format as well - e.g.

      select timeprop from version_table where timeprop = to_date('1.01.1900 11:12:13', 'dd.mm.yyyy hh24:mi:ss');

      Basically, no time alone in a query.
      Is this transformation always needed or am I missing anything from this game ?
      Server is 11gr1.
      Thanks.
        • 1. Re: How to match time alone (without date) in a query
          796440
          First order of business: What SQL type is the column? DATETIME or TIME?
          • 2. Re: How to match time alone (without date) in a query
            sperkmandl
            Column data type is DATE.
            Btw, it's DATE for all three variants: date, time, date+time.
            • 3. Re: How to match time alone (without date) in a query
              796440
              sperkmandl wrote:
              Column data type is DATE.
              Btw, it's DATE for all three variants: date, time, date+time.
              That sounds effed up. SQL has distinct DATE, TIME, and DATETIME types. If your column is of the correct type for what you're using it for, it should be pretty straightforward to get what you want. If it's not, well, you may still be able to get it to work, but I'll step away and wish you good luck.
              • 4. Re: How to match time alone (without date) in a query
                Peter Gjelstrup
                Hi sperkmand,

                Looks like you want to store your timeprop as INTERVAL DAY TO SECOND.

                Not a DATE which - as you found out - always have a day part and a time part.

                Regards
                Peter
                • 5. Re: How to match time alone (without date) in a query
                  jschellSomeoneStoleMyAlias
                  sperkmandl wrote:
                  Hi, I have a date colum named timeprop, where a time value has been inserted through:

                  DateFormat.getTimeInstance(DateFormat.MEDIUM, "de_DE").parse("11:12:13")
                  I can only suppose that means that you are not using setTime().


                  >
                  In SQL developer I can check the inserted value as:

                  select timeprop from version_table where timeprop is not null;
                  If you database is NOT Oracle then now would be the time to state that.


                  > Is this transformation always needed or am I missing anything from this game ?


                  The data type in the database DDL is relevant.

                  Oracle doesn't have any time only data types.

                  So if and only if you (and everyone else) has inserted time values in a consistent manner then you could use normal comparison by using setTime().

                  If that isn't true then you must craft Oracle PL/SQL to remove the time part of the timestamp values before you do your comparisions.
                  • 6. Re: How to match time alone (without date) in a query
                    796440
                    jschell wrote:
                    Oracle doesn't have any time only data types.
                    Oracle doesn't support TIME? Wow. I'm stunned. Or rather, I would be if I hadn't already been rendered cynical by all the other stuff they don't do, apparently because they're the 800-lb. gorilla and can get away with it.
                    • 7. Re: How to match time alone (without date) in a query
                      sperkmandl
                      According to the Oracle Jdbc dev. guide, both java.sql.Types.Date/Time map to DATE SQL data type.
                      I don't see any TIME datatype among Oracle primitive types.
                      In the above example, I inserted the column value by using setObject(), where arg was a Calendar instance having set proper time value and zero-epoch date value.
                      • 8. Re: How to match time alone (without date) in a query
                        796440
                        sperkmandl wrote:
                        In the above example, I inserted the column value by using setObject(), where arg was a Calendar instance having set proper time value and zero-epoch date value.
                        I would suspect that setTime() would work better.
                        • 9. Re: How to match time alone (without date) in a query
                          jschellSomeoneStoleMyAlias
                          sperkmandl wrote:
                          According to the Oracle Jdbc dev. guide, both java.sql.Types.Date/Time map to DATE SQL data type.
                          I don't see any TIME datatype among Oracle primitive types.
                          What is the data type in the database ?
                          In the above example, I inserted the column value by using setObject(), where arg was a Calendar instance having set proper time value and zero-epoch date value.
                          Doesn't sound like a good idea to me.

                          But as I stated if the only data in the database got there by that method then you can build a query where clause using the same methodology
                          • 10. Re: How to match time alone (without date) in a query
                            sperkmandl
                            Once again: the data type is DATE. This is how that column was created and this is what SQL dev. reports to me.

                            Concerning setObject(): it's a valid and generic way to feed data, I never had troubles with it, I use it also for UDT data.
                            Indeed the resulting value in the db is correct, showing a zero-epoch date.

                            I just wonder why the query subsystem - whenever finding a time alone in a clause - does not prefix it by the same zero-epoch date (1/1/1900).
                            Finally I did it myself (querying for the resulting full date instead of simple time) and this works fine (right match).

                            I still find it somewhat dirty, though.
                            • 11. Re: How to match time alone (without date) in a query
                              796440
                              sperkmandl wrote:
                              Concerning setObject(): it's a valid and generic way to feed data, I never had troubles with it,
                              Except that now you're using it and you're having trouble. That trouble may or may not be related to using the general setObject vs. the specifically tailored setTime, but if you don't do a little research and testing, you won't know.

                              I don't know the guts of how setObject works, but I wouldn't be surprised if at least part of its logic is related to the type of the column. I would speculate that, roughly, if the column type is TIME, setObject delegates to setTime, and if it's DATE, it delegates to setDate. I would further speculate that that delegating to setDate could cause problems when I want just a time.

                              Then I would do some research and testing to determine the validity of that speculation.

                              If you prefer to simply ignore it because you've never had troubles with setObject before, that's certainly your prerogative. It matters not to me.