HP Vertica SQL timestamp to number

Question

I'm trying to aggregate timestamps using STDDEV and I keep getting the error that it doesn't exist for the argument I'm providing it. Is there a way to convert timestamps in HP Vertica SQL to a int or number format or even unix timestamp.

I've tried:

select a, STDDEV(timestamp1) 
from mytable 
group by a

I get the error no function matches the given name an argument. I've used stddev before. I looked at the actual table and saw that though it's a time stamp field it looks to be represented as a date.

Answer 1

There isn't a way to take the stddev of a timestamp directly. You could maybe convert it to an epoch time, get the standard deviation, and then convert that to an interval. It would look something like this:

SELECT a, STDDEV( EXTRACT( epoch FROM timestamp1 ) ) * interval '1 second'
FROM mytable
GROUP BY a