Teradata Unixtime & Timestamp Convert，

Teradata Unixtime & Timestamp Convert，

SELECT  CURRENT_TIMESTAMP   , paypallib.unixtime2timestamp(  paypallib.timestamp2unixtime(CURRENT_TIMESTAMP)  )

SELECT *
FROM dbc.functions


SHOW FUNCTION  paypallib.timestamp2unixtime
SHOW FUNCTION  paypallib.unixtime2timestamp



REPLACE FUNCTION PAYPALLIB.unixtime2timestamp
(
    unixtime  BIGINT
)
RETURNS TIMESTAMP (0)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
 
RETURN  CAST (DATE '1970-01-01' + (unixtime / 86400) AS TIMESTAMP (0)) + ((unixtime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND)   

/* unixtime2timestamp */
;


REPLACE FUNCTION PAYPALLIB.timestamp2unixtime
(
    ts  TIMESTAMP
)
RETURNS BIGINT
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
 
RETURN  (CAST (ts AS DATE) - DATE '1970-01-01') * 86400 + EXTRACT (HOUR FROM ts) * 3600 + EXTRACT (MINUTE FROM ts) * 60 + EXTRACT (SECOND FROM ts)   

/* timestamp2unixtime */
;

REPLACE FUNCTION paypallib.month_id
(
    dt  DATE
)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
 
RETURN  EXTRACT (YEAR FROM ((dt - DATE '1900-01-01') YEAR (3) TO MONTH)) * 12 + EXTRACT (MONTH FROM ((dt - DATE '1900-01-01') YEAR (3) TO MONTH)) + 1
          
/* month_id */
;