Postgresql – Replace date function call with its functionality

postgresqlpostgresql-9.1

I have zero knowledge of postgresql somehow I have to extract data from a postgresql database

I am trying to replace function call in a query with its functionality.
Function:

-- Function: GetAppDate(bigint)  

-- DROP FUNCTION GetAppDate(bigint);  

CREATE OR REPLACE FUNCTION GetAppDate(bigint)
  RETURNS bigint AS
$BODY$
DECLARE
    offset int := extract(timezone_hour from current_timestamp); 
BEGIN
    return ($1/10 - 62135596800000000)/1000000 + (offset * (-3600));
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION GetAppDate(bigint)
  OWNER TO postgres;

Query:

 select GetAppDate(appt.startdatetime)
  from tablename appt

So far i have tried, I know this is wrong 🙁

 select ($1/10 - 62135596800000000)/1000000 + ((appt.startdatetime )  * (-3600)) 
 from tablename appt

Best Answer

You need to replace the $1 with the column name from the select:

The query:

select GetAppDate(appt.startdatetime)
from tablename appt

passes the column startdatetime to the function, so your select was almost there, but no quite - you forgot to replace the $1 and you incorrectly replaced the call to the function' variable with the column name:

select (startdatetime/10 - 62135596800000000)/1000000 + ((extract(timezone_hour from current_timestamp))  * (-3600)) 
from tablename appt

If you only have a single table in your query, then you do not need to use the table alias (the appt) to reference your column. If however your query contains more than one table, then you have to use the alias:

select (appt.startdatetime/10 - 62135596800000000)/1000000 ....
from tablename appt
  join other_table b on ...