This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,964 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Finding TIMESTAMP rows

BeefStu
BeefStu Member Posts: 351 Blue Ribbon

I'm experimenting with finding a range of TIMESTAMPs. As you can see below I can successfully retrieve rows when I use the 'between" method.


When I try to use >= and < operators I'm getting a syntax error. My question is it possible to use the second method to find TIMESTAMP rows? If so, how can I get the second query to work?

Thanks in advance to all who respond and your expertise.

CREATE TABLE TABLE_NAME(
   ts TIMESTAMP 
);

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742000', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742001', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742002', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742003', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742004', 'YYYY-MM-DD HH24:MI:SS.FF'));

SELECT *
  FROM TABLE_NAME 
 WHERE ts 
   BETWEEN TO_TIMESTAMP ('2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6')
       AND TO_TIMESTAMP ('2022-11-30 06:14:00.742003', 'yyyy-MM-dd HH24:mi:ss.ff6');

TS
30-NOV-22 06.14.00.742001 AM
30-NOV-22 06.14.00.742002 AM
30-NOV-22 06.14.00.742003 AM

Fingselect * from table_nane
 where ts >= 
'2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6' AND
ts < '2022-11-30 06:14:00.742004', 'yyyy-MM-dd HH24:mi:ss.ff6';


Tagged:

Best Answer

  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    Answer ✓

    select * from table_name

    where ts >= TO_TIMESTAMP ('2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6')

    and ts<=TO_TIMESTAMP ('2022-11-30 06:14:00.742003', 'yyyy-MM-dd HH24:mi:ss.ff6');

Answers

  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    Answer ✓

    select * from table_name

    where ts >= TO_TIMESTAMP ('2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6')

    and ts<=TO_TIMESTAMP ('2022-11-30 06:14:00.742003', 'yyyy-MM-dd HH24:mi:ss.ff6');

  • BeefStu
    BeefStu Member Posts: 351 Blue Ribbon

    @Saubhik wny do I need the the TO_TIMESTAMP if I have the fornat specified

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi, @BeefStu

    wny do I need the the TO_TIMESTAMP if I have the fornat specified

    'yyyy-MM-dd HH24:mi:ss.ff6' specifies the format if it is the 2nd argument of the TO_TIMESTAMP function. All by itself, it has no special meaning.

    If ts is a TIMESTAMP, then

    ts < '2022-11-30 06:14:00.742004', 'yyyy-MM-dd HH24:mi:ss.ff6';
    

    has two separate errors:

    1. you're trying to compare a TIMESTAMP to a string (that is, '2022-11-30 06:14:00.742004', and
    2. there is a comma and a stray expression (another string) after the comparison. ( X < Y   is good syntax;  X < Y, Z  is not.)


    BeefStu
  • BeefStu
    BeefStu Member Posts: 351 Blue Ribbon

    @Frank Kulash thanks for the explanation. I got it now

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    You need TO_TIMESTAMP when you use inequalities, for exactly the same reason you needed it in the INSERT statements, and in the query where you used the BETWEEN condition. You didn't ask why you need TO_TIMESTAMP in those cases; why would it be different when you use inequalities?

    For future reference, note that if the timestamps are hard-coded (and only in that case), you can use the simpler syntax provided by the "timestamp literal". That is:

    TO_TIMESTAMP ('2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6')
    

    can also be written as

    TIMESTAMP '2022-11-30 06:14:00.742001'
    

    Note that there are no parentheses here - this is a "literal" (a hard-coded, fixed value), not a function; and there is no format model. Rather, the "timestamp literal" has a fixed format that must be used, which is exactly the format you were using for TO_TIMESTAMP anyway.

    This does not work for variable strings though; for example, if you have a table with a VARCHAR2 column called "ts_str" (timestamps but in string data type, instead of the proper timestamp data type), you can't do this:

    TIMESTAMP ts_str   -- WRONG!  TIMESTAMP literal requires a literal string, not a variable
    
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    In this reply, in addition to showing how to use TO_TIMESTAMP, you changed the OP's requirement somewhat - changing a strict inequality to a non-strict one.

    This is, I speculate, just the opposite of what the OP was trying to do. BETWEEN is OK, but often what we want are time intervals closed at the lower end and open at the upper end; a "greater than or equal to" and a "strictly less than" inequalities. You can convert that to a BETWEEN condition sometimes (as you were suggesting in your answer), but that has to be done carefully. In the example at hand, what if the ts column has values with more than six decimal places for fractions of a second? The OP used ff6 in his format model for the filter conditions, but this doesn't necessarily mean the data has only six decimal places. The data the OP provided does, in fact, have no more than six decimal places, but we don't know what's in the real-life table.

    Saubhik