The Vertica Database Forums

Posted: **Tue Nov 19, 2013 8:27 pm**

Hi,
I'm hoping this is a very simple solution, I'm trying to find the type of queries being run on the database over a 10 minutes period, what is wrong with these 2 bits of SQL, both fail with syntax errors:-

select timestampadd(mi,-10,getdate()), request_type, count(*) from QUERY_REQUESTS
where start_timestamp <= 'timestampadd(mi, -10,getdate())' and
start_timestamp >= 'timestampadd (mi, -20,getdate())'
and request_type in ('LOAD','QUERY')
group by request_type
order by request_type;

select timestampadd(mi,-10,getdate()), request_type, count(*) from QUERY_REQUESTS
where start_timestamp between 'getdate() - INTERVAL '10 MINUTES'' and 'getdate() - INTERVAL '20 MINUTES''
and request_type in ('LOAD','QUERY')
group by request_type
order by request_type;

Any help is much appricated.

Regards
Tim

Posted: **Tue Nov 19, 2013 8:35 pm**

Maybe try removing those extra single quote characters

And for the second query, I think you need to switch the upper and lower bounds of your BETWEEN predicate.

Code: Select all

select timestampadd(mi,-10,getdate()), request_type, count(*) from QUERY_REQUESTS
where start_timestamp <= timestampadd(mi, -10,getdate()) and
start_timestamp >= timestampadd (mi, -20,getdate())
and request_type in ('LOAD','QUERY')
group by request_type
order by request_type;

Code: Select all

select timestampadd(mi,-10,getdate()), request_type, count(*) from QUERY_REQUESTS
where start_timestamp between getdate() - INTERVAL '20 MINUTES' and getdate() - INTERVAL '10 MINUTES'
and request_type in ('LOAD','QUERY')
group by request_type
order by request_type;

Posted: **Tue Nov 19, 2013 11:57 pm**

Thanks....just having a senior moment.

The Vertica Database Forums

Date Formats

Date Formats

Re: Date Formats

Re: Date Formats