Date Formats

Moderator: NorbertKrupa

Post Reply
Timbo
Intermediate
Intermediate
Posts: 53
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Date Formats

Post by Timbo » 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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Date Formats

Post by JimKnicely » Tue Nov 19, 2013 8:35 pm

Maybe try removing those extra single quote characters :D 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;
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Timbo
Intermediate
Intermediate
Posts: 53
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Re: Date Formats

Post by Timbo » Tue Nov 19, 2013 11:57 pm

Thanks....just having a senior moment.

Post Reply

Return to “New to Vertica SQL”