HGDB相容MySQL date_format函式
瀚高PG實驗室發表於2021-10-29
環境
系統平臺:
Linux x86-64 Red Hat Enterprise Linux 7
版本:
6.0,5.6.5,4.5,4.3.4.9
詳細資訊
-date_format函式主體: CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text) RETURNS text LANGUAGE plpgsql IMMUTABLE STRICT AS $function$ DECLARE i int := 1; temp text := ''; c text; n text; res text; BEGIN WHILE i <= pg_catalog.length($2) LOOP c := SUBSTRING ($2 FROM i FOR 1); IF c = '%' AND i != pg_catalog.length($2) THEN n := SUBSTRING ($2 FROM (i + 1) FOR 1); SELECT INTO res CASE WHEN n = 'a' THEN pg_catalog.to_char($1, 'Dy') WHEN n = 'b' THEN pg_catalog.to_char($1, 'Mon') WHEN n = 'c' THEN pg_catalog.to_char($1, 'FMMM') WHEN n = 'D' THEN pg_catalog.to_char($1, 'FMDDth') WHEN n = 'd' THEN pg_catalog.to_char($1, 'DD') WHEN n = 'e' THEN pg_catalog.to_char($1, 'FMDD') WHEN n = 'f' THEN pg_catalog.to_char($1, 'US') WHEN n = 'H' THEN pg_catalog.to_char($1, 'HH24') WHEN n = 'h' THEN pg_catalog.to_char($1, 'HH12') WHEN n = 'I' THEN pg_catalog.to_char($1, 'HH12') WHEN n = 'i' THEN pg_catalog.to_char($1, 'MI') WHEN n = 'j' THEN pg_catalog.to_char($1, 'DDD') WHEN n = 'k' THEN pg_catalog.to_char($1, 'FMHH24') WHEN n = 'l' THEN pg_catalog.to_char($1, 'FMHH12') WHEN n = 'M' THEN pg_catalog.to_char($1, 'FMMonth') WHEN n = 'm' THEN pg_catalog.to_char($1, 'MM') WHEN n = 'p' THEN pg_catalog.to_char($1, 'AM') WHEN n = 'r' THEN pg_catalog.to_char($1, 'HH12:MI:SS AM') WHEN n = 'S' THEN pg_catalog.to_char($1, 'SS') WHEN n = 's' THEN pg_catalog.to_char($1, 'SS') WHEN n = 'T' THEN pg_catalog.to_char($1, 'HH24:MI:SS') WHEN n = 'U' THEN pg_catalog.lpad(week($1::date, 0)::text, 2, '0') WHEN n = 'u' THEN pg_catalog.lpad(week($1::date, 1)::text, 2, '0') WHEN n = 'V' THEN pg_catalog.lpad(week($1::date, 2)::text, 2, '0') WHEN n = 'v' THEN pg_catalog.lpad(week($1::date, 3)::text, 2, '0') WHEN n = 'W' THEN pg_catalog.to_char($1, 'FMDay') WHEN n = 'w' THEN EXTRACT(DOW FROM $1)::text WHEN n = 'X' THEN pg_catalog.lpad(((_calc_week($1::date, _week_mode(2)))[2])::text, 4, '0') WHEN n = 'x' THEN pg_catalog.lpad(((_calc_week($1::date, _week_mode(3)))[2])::text, 4, '0') WHEN n = 'Y' THEN pg_catalog.to_char($1, 'YYYY') WHEN n = 'y' THEN pg_catalog.to_char($1, 'YY') WHEN n = '%' THEN pg_catalog.to_char($1, '%') ELSE NULL END; temp := temp operator(pg_catalog.||) res; i := i + 2; ELSE temp = temp operator(pg_catalog.||) c; i := i + 1; END IF; END LOOP; RETURN temp; END $function$; --主體函式呼叫的其它函式 CREATE OR REPLACE FUNCTION _week_mode(mode integer) RETURNS integer AS $$ DECLARE _WEEK_MONDAY_FIRST CONSTANT integer := 1; _WEEK_FIRST_WEEKDAY CONSTANT integer := 4; week_format integer := mode & 7; BEGIN IF (week_format & _WEEK_MONDAY_FIRST) = 0 THEN week_format := week_format # _WEEK_FIRST_WEEKDAY; END IF; RETURN week_format; END; $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION _calc_weekday(qdate date, sundayfirst boolean) RETURNS integer AS $$ BEGIN RETURN (EXTRACT(DOW FROM qdate)::integer + CASE WHEN sundayfirst THEN 0 ELSE 6 END) % 7; END; $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION _calc_days_in_year(year integer) RETURNS integer AS $$ BEGIN IF (year & 3) = 0 AND ((year % 100) <> 0 OR (year % 400) = 0 AND year <> 0) THEN RETURN 366; ELSE RETURN 365; END IF; END; $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION _calc_week(qdate anyelement, behavior integer) RETURNS integer[] AS $$ DECLARE _WEEK_MONDAY_FIRST CONSTANT integer := 1; _WEEK_YEAR CONSTANT integer := 2; _WEEK_FIRST_WEEKDAY CONSTANT integer := 4; qyear integer := EXTRACT(YEAR FROM qdate); qmonth integer := EXTRACT(MONTH FROM qdate); qday integer := EXTRACT(DAY FROM qdate); daynr integer := EXTRACT(DOY FROM qdate); yday1 date := pg_catalog.date_trunc('year', qdate); first_daynr integer := 1; monday_first boolean := (behavior & _WEEK_MONDAY_FIRST) <> 0; week_year boolean := (behavior & _WEEK_YEAR) <> 0; first_weekday boolean := (behavior & _WEEK_FIRST_WEEKDAY) <> 0; weekday integer := _calc_weekday(yday1, NOT monday_first); days integer; BEGIN IF qmonth = 1 AND qday <= 7 - weekday THEN IF (NOT week_year) AND ((first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4)) THEN RETURN array[0, qyear]; END IF; week_year := true; qyear := qyear - 1; days := _calc_days_in_year(qyear); first_daynr := first_daynr - days; weekday := (weekday + 53 * 7 - days) % 7; END IF; IF (first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4) THEN days := daynr - (first_daynr + (7 - weekday)); ELSE days := daynr - (first_daynr - weekday); END IF; IF week_year AND days >= 52 * 7 THEN weekday := (weekday + _calc_days_in_year(qyear)) % 7; IF (NOT first_weekday AND weekday < 4) OR (first_weekday AND weekday = 0) THEN qyear := qyear + 1; RETURN array[1, qyear]; END IF; END IF; RETURN array[days / 7 + 1, qyear]; END; $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION week(anyelement, integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT AS $function$ BEGIN IF is_datetime ( $1 ) THEN RETURN (_calc_week($1, _week_mode($2)))[1]; END IF; RAISE EXCEPTION 'Invalid date / time value --> %', $1; END; $function$;--呼叫函式: select DATE_FORMAT(now()::timestamp ,'%Y-%m-%d'); select DATE_FORMAT(now()::timestamp,'%Y-%m-%d %H:%i'); select DATE_FORMAT(now()::timestamp,'%Y'); select DATE_FORMAT(now()::timestamp,'%Y-%m'); select DATE_FORMAT(now()::timestamp,'%m-%d'); select DATE_FORMAT(now()::timestamp,'%m-%d %H:%i'); select DATE_FORMAT(now()::timestamp,'%Y年%m月%d日'); select DATE_FORMAT(now()::timestamp,'%Y-%m-%d %H:%i:%s');
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69994931/viewspace-2839739/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql DATE_FORMAT函式2016-11-28MySqlORM函式
- 瀚高DB相容MySQL if函式2021-12-22MySql函式
- MySQL內建函式:year()、 week()相容2022-05-23MySql函式
- 相容MySQL中的find_in_set函式2022-01-27MySql函式
- TypeScript 函式相容2018-08-12TypeScript函式
- 瀚高資料庫相容Mysql的unhex函式2021-11-11資料庫MySql函式
- Mysql的DATE_FORMAT()應用2015-08-06MySqlORM
- Mysql date_format 與 Oracle to_char(date,’format’)2012-05-08MySqlORMOracle
- MySQL 函式2024-05-24MySql函式
- MySQL函式2024-05-26MySql函式
- Mysql 常用函式(15)- upper 函式2020-05-15MySql函式
- 【Mysql 學習】日期函式函式2011-01-01MySql函式
- MySQL 常用函式2018-10-25MySql函式
- MySQL 常用函式。2020-12-07MySql函式
- MySQL函式(一)2020-10-18MySql函式
- MySQL常用函式2014-09-22MySql函式
- mysql拼接函式2016-05-27MySql函式
- MySQL函式大全(字串函式,數學函式,日期函式,系統級函式,聚合函式)2020-11-14MySql函式字串
- PostgreSQLOracle相容性-Analysis函式之keep2018-06-21SQLOracle函式
- 相容所有瀏覽器的getElementsByClassName()函式2017-04-13瀏覽器函式
- PostgreSQL核心自帶的Oracle相容函式2017-02-09SQLOracle函式
- MySQL函式學習(一)-----字串函式2022-01-23MySql函式字串
- MySQL函式-條件判斷函式2018-03-05MySql函式
- Mysql 常用函式(1)- 常用函式彙總2020-05-14MySql函式
- mysql FIND_IN_SET函式、INSTR函式2020-10-13MySql函式
- MySQL 內建函式2018-12-18MySql函式
- Mysql視窗函式2020-10-14MySql函式
- Mysql內建函式2017-05-17MySql函式
- Mysql 的trim() 函式2014-12-12MySql函式
- 7mysql函式2011-01-07MySql函式
- MySQL時間函式2011-05-13MySql函式
- MYSQL的字串函式2007-02-03MySql字串函式
- MySQL 聚合函式大全2015-10-09MySql函式
- MySQL 控制流函式2015-10-09MySql函式
- MySQL 字串函式大全2015-10-09MySql字串函式
- 《MySQL 入門教程》第 16 篇 MySQL 常用函式之日期函式2022-02-09MySql函式
- Mysql 常用函式(20)- ceiling 函式2020-05-16MySql函式
- mysql 自定義分析函式 least 及 日期函式2009-12-22MySql函式AST