Using functions in Vertica SQL

[rishabh101]

Hello,

I am trying to use various time related functions like timestampdiff() etc. Whenever I use it I get an error that either this function doesn't exist or I don't have required permissions. I guess I have required permissions as I am using admin login.

select timestampdiff(second,end_time,start_time) from table1;

Kindly help me with the same. Also please let me know where I can find glossary and help on all functions. I am using CE version.

Thanks and Regards
Rishabh

[JimKnicely]

Hi,

Looks like you are using the correct syntax for the function.

From the docs the general format is:

• TIMESTAMPDIFF ( datepart , starttimestamp , endtimestamp );

Example:

Code: Select all

dbadmin=> select timestampdiff(second, date '2012-12-26', date '2012-12-27');
 timestampdiff
---------------
         86400
(1 row)

Keep in mind the values you pass into the starttimestamp and endtimestamp parameters must be a timestamp or something that can be converted to a timestamp. If I pass in an integer value, I'll get the error you seem to be getting:

Code: Select all

dbadmin=> select timestampdiff(second, date '2012-12-26', 1);
ERROR 3457:  Function catalog.timestampdiff(unknown, date, int) does not exist, or permission is denied for catalog.timestampdiff(unknown, date, int)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

You can find the online docs for CE at my.vertica.com. For instance, here is a link to the 5.1 release online docs:

http://my.vertica.com/docs/CE/5.1.0/HTM ... #16886.htm

I hope this helps!

[rishabh101]

Thanks Jim, that was really helpful.

Regards
Rishabh