I just had a need for a function that takes a datetime year to second and returns the number of seconds since January 1, 1970.
That would be easy to do by writing a "C" UDR but I did not want to deal with compiling and installing a shared library so I decided to approach it as an SPL routine.
Not that it is a great thing but I thought I'd share it with whoever needs it. Let me know if you find this useful:
CREATE FUNCTION epoch(dt datetime year to second)
DEFINE dt_varchar varchar(20);
DEFINE mm, dd, yy, days, hh, mi, ss integer;
LET mm = MONTH(dt);
LET dd = DAY(dt);
LET yy = YEAR(dt);
LET days = MDY(mm, dd, yy) - MDY(1, 1, 1970);
LET dt_varchar = dt;
LET hh = substr(dt_varchar, 12, 2);
LET mi = substr(dt_varchar, 15, 2);
LET ss = substr(dt_varchar, 18, 2);
return (days * 86400) + (hh * 3600) + (mi * 60) + ss;
You can use it either in an SQL statement or directly with EXECUTE FUNCTION. For example:
EXECUTE FUNCTION epoch("2008-11-25 08:32:45");
1 row(s) retrieved.