I never worked with pgsql before and im trying to convert this function to Mysql... can you give me a hand?
create or replace function hot(ups integer, downs integer, date timestamp with time zone) returns numeric as $$
select round(cast(log(greatest(abs($1 - $2), 1)) + sign($1 - $2) * (date_part('epoch', $3) - 1134028003) / 45000.0 as numeric), 7) $$ language sql immutable
So far i've got this but it still gives me an error
create function hot(ups MEDIUMINT, downs MEDIUMINT, date timestamp) returns BIGINT
select round(cast(log(greatest(abs($1 - $2), 1)) + sign($1 - $2) * (unix_timestamp(date) - 1134028003) / 45000.0 BIGINT), 7)
$$ language sql immutable
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIGINT), 7) $$ language sql immutable' at line 2"
That defines the function
hot(ups, downs, date) (where
downs are integers and
date is a timestamp-with-time-zone) as this SQL statement:
SELECT ROUND( CAST( LOG(GREATEST(ABS(ups - downs), 1)) + SIGN(ups - downs) * (DATE_PART('epoch', date) - 1134028003) / 45000.0 AS NUMERIC ), 7 ) ;
$$ ... $$ notation is a PostgreSQL notation for a multiline string (in this case, containing the SQL query that defines the function). The
LANGUAGE SQL indicates that the function is in SQL (and not, for example, PL/pgSQL). The
IMMUTABLE (see http://www.postgresql.org/docs/8.4/static/sql-createfunction.html) indicates that the function doesn't modify the database, and always returns the same results for the same arguments. (In other words, it's a "pure" function.)
sign, see http://www.postgresql.org/docs/8.4/static/functions-math.html.
CAST(... AS NUMERIC)returns its argument as a number (for example,
CAST('123' AS NUMERIC)is
greatest, see http://www.postgresql.org/docs/8.4/static/functions-conditional.html.
DATE_PART('epoch', ...)returns its argument (a date) as a number of seconds since
1970-01-01 00:00:00.000 GMT.