CODEWITHSUNDEEP
mysqlschema
Dominik Gacek
Dominik Gacek

Reputation: 43

mysql version of reddit pgsql hotness function

Firstly sorry for my english, it's my first post here, and my english isn't as well as i wish but i hope it'll be enough to get a answer.

So how some of you maybe now reddit put their own source code on github and i want to use (a little modified by me) version of sql schema with a hotness algorithm. The problem is that schema is written in psgsql and my database use mysql engine.

I tried to convert schema manually but i give up with no effects, so i try again with misc tools and apps, but not even one of them support converting of procedures & functions, and the problem is that i need exactly just that one option.

So, is anyone of you can help me convert the hotness function from there:

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;

to mysql schema, i would be very grateful :)

Once again sorry for my language, i now that i underestimates the standard :)

Upvotes: 2

Views: 609

Answers (2)

schteppe
schteppe

Reputation: 2084

I just wrote this function below, and it seem to work.

CREATE FUNCTION hot (ups INT(10),downs INT(10),d TIMESTAMP)
RETURNS DOUBLE DETERMINISTIC
RETURN ROUND(LOG(GREATEST(ABS(ups-downs), 1)) + SIGN(ups-downs)*(UNIX_TIMESTAMP(d) - 1134028003) / 45000.0,7);

I compared its outputs to the outputs of this Python code, everything seem OK.

Sample run:

SELECT hot(20,10,'2013-01-01 00:00:00');

Query OK, 0 rows affected (0.00 sec)

+----------------------------------+
| hot(20,10,'2013-01-01 00:00:00') |
+----------------------------------+
|                     4957.1202962 |
+----------------------------------+
1 row in set (0.00 sec)

Upvotes: 4

A.H.
A.H.

Reputation: 66273

I don't know the MySQL syntax for user defined functions, but some PostgreSQL specific parts are:

date_part('epoch', $3)

Number of seconds of $3 since the epoch i.e. since 1970-01-01 00:00:00.

1134028003

Number of seconds from epoch to 2005-12-08 07:46:43.

Perhaps this is useful for finding MySQL equivalents.

Upvotes: 1

Related Questions