Custom time conversion functions in Greenplum

Recent database Schema tuning, a part of the business from MySQL migrated to Greenplum up, originally MySQL with Unix_timestamp and from_unixtime two functions can achieve standard Time and Unix time of mutual conversion, Turning over the Greenplun document, no similar functions were found, so we used Python to customize these two functions, and implemented two business-related functions on the basis of these two functions, which are recorded here.

1, first create a python language. testdb=# create language plpythonu; Create language2, create Unix_timestamp function, convert standard Time to Unix time create or replace function unix_ timestamp  (Timestamp_minutes timestamp)  returns float as $ $import   Timereturn time.mktime (Time.strptime (timestamp_minutes,  '%y-%m-%d %h:%m:%s ')) $ $language   Plpythonu volatile security definer;3, create From_unixtime function, convert Unix time to standard time create or  replace function from_unixtime  (Unix int)  returns timestamp as $$ Import timereturn time.strftime ('%y-%m-%d %h:%m:%s ',  time.localtime (Unix)) $ $language   Plpythonu volatile security definer;4, create a business-related function Nl_to_timestamp, convert standard Time to Unix time Create or  replace function nl_to_timestamp  (Timestamp_minutes timestamp)  returns  float as $ $select   (Floor (Unix_timestamp)  / 60)  - 22616640)  as unixtime$ $language  sql;5, create a business-related function nl_to_date, convert UNIX time to standard time create  or replace function nl_to_date  (Timestamp_minutes int)  returns  timestamp as$ $select  from_unixtime (($1 + 22616640)  * 60)  as datetime$$ Deleting a function in Language sql;6, Greenplum requires specifying the parameter type of the function testdb=# drop function from_unixtime (int);D ROP  function

This article from "Brave forward, resolutely left" blog, declined reprint!

Custom time conversion functions in Greenplum