how to use the datepart function to calculate the count at specific time?

Moderator: NorbertKrupa

Post Reply
atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

how to use the datepart function to calculate the count at specific time?

Post by atul.pdm@gmail.com » Tue Nov 22, 2016 7:43 pm

I need to calculate the count of slots for each hour like 2016-01-01 00:00:00,2016-01-01 01:00:00,..........

I tried using below query but it did not worked,please suggest

Code: Select all

SELECT count(slots) FROM acctn 
WHERE  (DATEPART(mm, start_time) = 00 AND (DATEPART(ss, start_time) = 00

output should be like:

Date   					count
 2016-01-01 00:00:00			value
 2016-01-01 01:00:00			value


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

Re: how to use the datepart function to calculate the count at specific time?

Post by JimKnicely » Tue Nov 22, 2016 9:50 pm

This is one way:

Code: Select all

select count(slots) as slots_cnt,
       to_char(start_time, 'DD-MON-YYYY HH24') as date_hr
  from acctn
 group by 2
 order by 2;
Jim Knicely

Image

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

Post Reply

Return to “New to Vertica SQL”