CODEWITHSUNDEEP
sqldatabaseoracle-databasetimestamp
Umut TEKİN
Umut TEKİN

Reputation: 962

Oracle DB Query Timestamp with Like and '%'

I want to list all records that whose TARGET_COMMIT field days between 10/Nov/2018 and 18/Nov/2018 at around 5.00 AM. I have been using this query:

select * from (select * from GGS_ADMIN.GGS_HEARTBEAT_HISTORY  
where DELGROUP='REPDELTA' and TARGET_COMMIT between '10/Nov/2018' 
and '18/Nov/2018') where TARGET_COMMIT like '%/Nov/2018 5:%:%.% AM';

Here is one record of my inner query result:

SBLPROF,    EDELTA, 11/10/2018 5:56:22.064830 AM,   11/10/2018 
5:56:27.495548 AM,
11/10/2018 5:56:24.731541 AM,   2.666711,               11/10/2018 
5:56:26.305759 AM,  REPDELTA    ,   5.430718,   3,  11/10/2018 
5:56:22.820934 AM,  0,  14441,  0,  14441

And here "11/10/2018 5:56:27.495548 AM" is my TARGET_COMMIT field.

If use 

select * from (select * from GGS_ADMIN.GGS_HEARTBEAT_HISTORY where 
DELGROUP='REPDELTA' and TARGET_COMMIT between '10/Nov/2018'  and 
'18/Nov/2018') where TARGET_COMMIT like '11/10/2018 5:56:27.495548 AM'

instead of my previous query it matches and lists the result. Why I can't use the "like" function and "%" for timestamp?

By the way here is my dual result:

select sysdate from dual; 

  SYSDATE                 
11/18/2018 04:11:53 PM 
1 row selected.

Thanks in advance!

Upvotes: 0

Views: 1596

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You may use TIMESTAMP literal and EXTRACT.Preferably use >= and < instead of BETWEEN for ranges.

SELECT *
FROM ggs_admin.ggs_heartbeat_history
   WHERE delgroup = 'REPDELTA' AND 
   target_commit    >=  TIMESTAMP '2018-11-10 00:00:00' 
AND target_commit   <   TIMESTAMP '2018-11-18 00:00:00' + INTERVAL '1' DAY
AND EXTRACT ( HOUR FROM target_commit) = 5

If you want a desired format, you may also use

WHERE    
target_commit >=  TO_TIMESTAMP('10/Nov/2018','dd/mon/yyyy')  AND 
target_commit <   TO_TIMESTAMP('18/Nov/2018','dd/mon/yyyy') + INTERVAL '1' DAY
AND EXTRACT ( HOUR FROM target_commit) = 5

Upvotes: 2

Related Questions