UPDATE 17nov2010: I Change the URL of my uberfancyfull game, give it a try.. although its in Alpha Stage.
In my game i need to do a lot of date and time calculations which i prefer to do on the database instead of doing it on PHP. But unfortunately (or fortunately?) i had to change my DB from a Postgres DB to a Mysql DB a while ago.. but i finally bought a hosting with Posgtres 8.3.X. So i had been practicing in getting the same data stuff from the two databases.
And here comes the interesting stuff. Do you ever needed to get the difference between 2 timestamps and get it on seconds? Well let me show you the way:
MYSQL Way:
SELECT TIME_TO_SEC(TIMEDIFF(example_time,now())) AS clock FROM movements
Ok let go step by step(uh baby!!):
1) TIMEDIFF(example_time,now()) This will get the difference between example_time and now(). As you know now() get the actual time in the server. So lets say example_time is '2009/10/10 23:10:10' and now() give us '2009/10/10 23:05:05' the result will be '00:05:05'
, Thats ok but we need it in seconds so.
2) TIME_TO_SEC(time) This function get a time value and return that number in secods.. so in our example it will give us 5*60 +5 = 395 seconds.
POSTGRES Way:
SELECT EXTRACT(EPOCH FROM example_time - now())::integer AS clock FROM movements;
Thast totally different from Msql .. let me give you a brief explanation fo the sintax:
1) EXTRACT(EPOCH FROM example_time - now()) The EXTRACT function is a tricky one, it will extract some part of the timestamp invlolved here , in this case the rest of example_time minus now(). Putting EPOCH first make the EXTRACT function transforms the answer of example_time - now() into seconds . Be carefull with putting SECOND instead of EPOCH.. that will extract only the secods of the answer (from 1 to 59), so in our example with EPOCH we will get 395.45.. and with SECONDS we will get 5.45..
2) EXTRACT(...)::integer This will transform our answer into integers so intead of 395.45.. we will get 395.And just btw.. this will not round our answer, it will get just the integer part of the result.
A POSTGRES Frebbie anyone?:
This is a Postgres Function I created to simulate the TIME_TO_SEC() funtion in mysql. USe it free of charge ;):
CREATE OR REPLACE FUNCTION "public"."time_to_sec" (first timestamp, second timestamptz) RETURNS integer AS
$body$
DECLARE
segundos integer;
BEGIN
segundos = EXTRACT(EPOCH FROM first - second)::integer;
return segundos;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
