四平网站建设公司,珠宝销售网站源码,win10系统优化工具,深圳网站建设公司推荐乐云seo一、在使用 date_format、from_unixtime、to_days、yearweek 函数时#xff0c;Gbase 8s 数据库不支持#xff0c;可以使用创建 UDR 外部函数来实现
二、登录命令控制台或者使用 navicat 连接 Gbase 数据库
这里使用 navicat #xff0c;点击新增连接选择 PostGreSql 驱动…一、在使用 date_format、from_unixtime、to_days、yearweek 函数时Gbase 8s 数据库不支持可以使用创建 UDR 外部函数来实现
二、登录命令控制台或者使用 navicat 连接 Gbase 数据库
这里使用 navicat 点击新增连接选择 PostGreSql 驱动添加地址、账号、密码 连接数据库后选中目标库选中目标模式再点击函数-新增函数执行以下语句即可 注意这里 选中 public 模式使用 mss 用户自行修改函数中对应的内容 例如FUNCTION “public”.“date_format”、OWNER TO “mss” date_format 函数 CREATE OR REPLACE FUNCTION public.date_format(ctimestamp timestamptz, informate varchar)RETURNS pg_catalog.varchar AS $BODY$-- Routine body goes here...DECLARE result_current_date varchar;BEGIN
-- IF upper($1) upper(YYYY-MM-DD) || upper($1) upper(%Y-%M-%D) THEN
-- SELECT to_char(now(),YYYY-MM-DD) into result_current_date;
-- END IF;
--
--
-- IF upper($1) upper(%Y-%M-%D %h:%m) || upper($1) upper(%Y-%M-%D %h:%m) THEN
-- SELECT to_char(now(),YYYY-MM-DD HH:mm) into result_current_date;
-- END IF;
--
-- IF upper($1) upper(%Y-%M-%D %h:%m:%s) || upper($1) upper(%Y-%M-%D %h:%m:%s) THEN
-- SELECT to_char(now(),YYYY-MM-DD HH:mm:ss) into result_current_date;
-- END IF;case upper($2)when upper(%Y) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY) into result_current_date;when upper(%Y-%M) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY-MM) into result_current_date; when upper(%Y-%M-%D) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY-MM-DD) into result_current_date;when upper(%Y-%M-%D %h) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY-MM-DD HH24) into result_current_date;when upper(%Y-%M-%D %h:%m) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY-MM-DD HH24:MI) into result_current_date;when upper(%Y-%M-%D %h:%m:%s) thenSELECT to_char(smalldatetime_to_timestamp($1),YYYY-MM-DD HH24:MI:ss) into result_current_date; when upper(%M) thenSELECT to_char(smalldatetime_to_timestamp($1),MM) into result_current_date; when upper(%M-%D) thenSELECT to_char(smalldatetime_to_timestamp($1),MM-DD) into result_current_date;when upper(%D) thenSELECT to_char(smalldatetime_to_timestamp($1),DD) into result_current_date;when upper(%h) thenSELECT to_char(smalldatetime_to_timestamp($1),HH24) into result_current_date; when upper(%h:%m) thenSELECT to_char(smalldatetime_to_timestamp($1),HH24:MI) into result_current_date; when upper(%m) thenSELECT to_char(smalldatetime_to_timestamp($1),MI) into result_current_date; when upper(%m:%s) thenSELECT to_char(smalldatetime_to_timestamp($1),MI:ss) into result_current_date; when upper(%s) thenSELECT to_char(smalldatetime_to_timestamp($1),ss) into result_current_date; when upper(%h:%m:%s) thenSELECT to_char(smalldatetime_to_timestamp($1),HH24:MI:ss) into result_current_date; elseSELECT to_char(smalldatetime_to_timestamp($1),informate) into result_current_date;end case;RETURN result_current_date;
END$BODY$LANGUAGE plpgsql VOLATILECOST 100;ALTER FUNCTION public.date_format(ctimestamp timestamptz, informate varchar) OWNER TO mss;查询语句 SELECT date_format(now(),%Y-%M-%D %h:%m:%s);from_unixtime 函数 CREATE OR REPLACE FUNCTION public.from_unixtime(t int8)RETURNS pg_catalog.timestamp AS $BODY$DECLARE result_current_date timestamp;BEGIN select TO_TIMESTAMP(t) into result_current_date;RETURN result_current_date;
END; $BODY$LANGUAGE plpgsql VOLATILECOST 100;ALTER FUNCTION public.from_unixtime(t int8) OWNER TO mss;查询语句 select from_unixtime(1692328679);to_days 函数 -- 参数 varchar类型
CREATE OR REPLACE FUNCTION public.to_days(ctimestamp varchar)RETURNS pg_catalog.int4 AS $BODY$-- Routine body goes here...DECLARE result_current_date int4;BEGINSELECT TIMESTAMPDIFF(day, 0001-01-01, $1) into result_current_date;RETURN result_current_date;
END$BODY$LANGUAGE plpgsql VOLATILECOST 100;ALTER FUNCTION public.to_days(ctimestamp varchar) OWNER TO mss;-- 参数 timestamptz 类型
CREATE OR REPLACE FUNCTION public.to_days(ctimestamp timestamptz)RETURNS pg_catalog.int4 AS $BODY$-- Routine body goes here...DECLARE result_current_date int4;BEGINSELECT TIMESTAMPDIFF(day, 0001-01-01, $1) into result_current_date;RETURN result_current_date;
END$BODY$LANGUAGE plpgsql VOLATILECOST 100;ALTER FUNCTION public.to_days(ctimestamp timestamptz) OWNER TO mss;查询语句 select to_days(now());yearweek 函数 CREATE OR REPLACE FUNCTION public.yearweek(ctimestamp timestamptz)RETURNS pg_catalog.int4 AS $BODY$-- Routine body goes here...DECLARE week_n int4;year_n int4;BEGINSELECT to_char(smalldatetime_to_timestamp($1),YYYY) into year_n;SELECT trunc(1 (smalldatetime_to_timestamp($1) - TRUNC(smalldatetime_to_timestamp($1), YEAR)) / 7) into week_n;RETURN ((year_n*100)week_n);
END$BODY$LANGUAGE plpgsql VOLATILECOST 100;ALTER FUNCTION public.yearweek(ctimestamp timestamptz) OWNER TO mss;查询语句 select YEARWEEK(now());
select YEARWEEK(2023-01-03 12);
