一、db2->gaussdb函式轉換
問題描述：使用GaussDB替代DB2的方案，使用起來還是有些差別，做一下函式的對映轉換。
|
DB2寫法 |
GaussDB改寫語法 |
|
|
日期函式 |
days(OUTWORKDATE) |
EXTRACT(epoch from outworkdate) /86400; EXTRACT(DAY FROM (OUTWORKDATE - DATE '0001-01-01' + INTEGER '1' )) |
|
|
day(OUTWORKDATE) |
EXTRACT(DAY FROM outworkdate); DATE_PART('DAY', OUTWORKDATE) |
|
|
month(current_date) |
EXTRACT(MONTH FROM current_date); DATE_PART('MONTH', TRUNC(CURRENT_DATE)) |
|
|
year(current_date) |
EXTRACT(YEAR FROM outworkdate); DATE_PART('YEAR', TRUNC(CURRENT_DATE)) |
|
|
CURRENT_DATE -1 DAY/MONTH/YEAR |
CURRENT_DATE - INTERVAL '1 day' AS yesterday, CURRENT_DATE - INTERVAL '1 month' AS last_month, CURRENT_DATE - INTERVAL '1 year' AS last_year; TRUNC(CURRENT_DATE) - INTERVAL '1 DAY' AS yesterday, TRUNC(CURRENT_DATE) - INTERVAL '1 MONTH' AS last_month, TRUNC(CURRENT_DATE) - INTERVAL '1 YEAR' AS last_year;
|
|
|
CURRENT_DATE -1 DAY -1 MONTH |
current_date - interval '1 DAY' - interval '1 MONTH' |
|
|
to_char(CURRENT_DATE,'yyyy-mm') |
to_char(current_date,'yyyy-mm');to_char(trunc(current_date),'yyyy-mm') |
|
|
TIMESTAMPDIFF(2,END_TIME - START_TIME) |
extract(epoch from (end_time - start_time)); trunc(extract(epoch from (end_time - start_time))); TIMESTAMPDIFF()函式只在mysql模式中可以使用 |
|
資料型別轉化 |
dec(RATE,10,2) |
rate::numeric(10,2); cast(rate as numeric(10,2)); |
|
|
int(100.123) |
cast(100.123 as int); 100.123::int; |
|
|
char(1) |
cast(1 as char(1)) |
|
|
to_date() |
to_date() |
|
|
to_char() |
to_char() |
|
其他常用函式 |
LOCATE('|','115|56',1) |
INSTR('115|56', '|', 1) |
|
|
listagg(name,',') |
listagg(b,';') within group (order by b) |
|
|
nvl() |
nvl() |
|
|
trim() |
trim(BOTH 'x' from 'xTommxx'); trim(leading 'x' from 'xTommxx'); trim(trailing 'x' from 'xTommxx'); |
|
|
substr() |
substr('stringtest' from 4); substr('stringtest',4); |
|
|
VALUE() |
COALESCE() |
|
|
floor() |
floor() |
|
|
round() |
round() |
|
|
coalesce(company,0) |
coalesce(company,0) |
|
|
length() |
length() |
|
|
POSSTR(EXP1,EXP2) |
position(exp1,exp2) |
|
|
REPLACE (EXP1,EXP2,EXP3) |
replace(exp1,exp2,exp3) |
二、測試過程
日期函式
days(outworkdate) 函式轉換***************************************************************************** create table employee(id serial primary key,name varchar(100),outworkdate date); insert into employee(name,outworkdate) values('alice','2023-01-15'); insert into employee(name,outworkdate) values('bob','2023-02-20'); insert into employee(name,outworkdate) values('david','2023-04-05'); insert into employee(name,outworkdate) values('eve','2023-05-25'); testdb_0903=> select name,outworkdate,extract(epoch from outworkdate) / 86400 as days_since_epoch from employee; name | outworkdate | days_since_epoch -------+---------------------+------------------ alice | 2023-01-15 00:00:00 | 19372 bob | 2023-02-20 00:00:00 | 19408 david | 2023-04-05 00:00:00 | 19452 eve | 2023-05-25 00:00:00 | 19502 (4 rows) testdb_0903=> select name,outworkdate,extract(day from (outworkdate-date '1970-01-01'+integer '1')) as days_since_epoch from employee; name | outworkdate | days_since_epoch -------+---------------------+------------------ alice | 2023-01-15 00:00:00 | 19373 bob | 2023-02-20 00:00:00 | 19409 david | 2023-04-05 00:00:00 | 19453 eve | 2023-05-25 00:00:00 | 19503 (4 rows) day(outworkdate) 函式轉換***************************************************************************** testdb_0903=> select name,outworkdate,extract(day from outworkdate) as day_of_month from employee; name | outworkdate | day_of_month -------+---------------------+-------------- alice | 2023-01-15 00:00:00 | 15 bob | 2023-02-20 00:00:00 | 20 david | 2023-04-05 00:00:00 | 5 eve | 2023-05-25 00:00:00 | 25 (4 rows) testdb_0903=> testdb_0903=> testdb_0903=> select name,outworkdate,date_part('DAY',outworkdate) as day_of_month from employee; name | outworkdate | day_of_month -------+---------------------+-------------- alice | 2023-01-15 00:00:00 | 15 bob | 2023-02-20 00:00:00 | 20 david | 2023-04-05 00:00:00 | 5 eve | 2023-05-25 00:00:00 | 25 (4 rows) month(current_date) 函式轉換***************************************************************************** testdb_0903=> select extract(month from current_date) from dual; date_part ----------- 9 (1 row) testdb_0903=> select date_part('MONTH',trunc(current_date)) from dual; date_part ----------- 9 (1 row) year(current_date) 函式轉換***************************************************************************** testdb_0903=> select extract(year from current_date) from dual; date_part ----------- 2024 (1 row) testdb_0903=> testdb_0903=> select date_part('YEAR',trunc(current_date)) from dual; date_part ----------- 2024 (1 row) current_date -1 DAY/MONTH/YEAR 函式轉換***************************************************************************** testdb_0903=> select current_date as today, current_date - interval '1 day' as yesterday, current_date - interval '1 month' as last_month, current_date - interval '1 year' as last_year from dual; today | yesterday | last_month | last_year ------------+---------------------+---------------------+--------------------- 2024-09-29 | 2024-09-28 00:00:00 | 2024-08-29 00:00:00 | 2023-09-29 00:00:00 (1 row) testdb_0903=> testdb_0903=> select trunc(current_date) - interval '1 DAY' as yesterday, trunc(current_date) - interval '1 month' as last_month, trunc(current_date) - interval '1 year' as last_year from dual; yesterday | last_month | last_year ---------------------+---------------------+--------------------- 2024-09-28 00:00:00 | 2024-08-29 00:00:00 | 2023-09-29 00:00:00 (1 row) current_date -1 DAY -1 MONTH 函式轉換***************************************************************************** testdb_0903=> select current_date - interval '1 DAY' - interval '1 MONTH' as result_date from dual; result_date --------------------- 2024-08-28 00:00:00 (1 row) testdb_0903=> testdb_0903=> select trunc(current_date) - interval '1 DAY' - interval '1 MONTH' as result_date from dual; result_date --------------------- 2024-08-28 00:00:00 (1 row) to_char(current_date,'yyyy-mm') 函式轉換***************************************************************************** testdb_0903=> select to_char(current_date,'yyyy-mm') from dual; to_char --------- 2024-09 (1 row) testdb_0903=> select to_char(trunc(current_date),'yyyy-mm') from dual; to_char --------- 2024-09 (1 row) timestampdiff(2,end_time-start_time) 函式轉換***************************************************************************** GaussDB同名函式僅在MySQL模式資料庫中有效。 testdb_0903=> create table events(id serial primary key,start_time timestamp,end_time timestamp); testdb_0903=> insert into events(start_time,end_time) values('2023-10-01 10:00:00','2023-10-01 10:30:00'); testdb_0903=> insert into events(start_time,end_time) values('2023-10-01 11:00:00','2023-10-01 12:15:00'); testdb_0903=> insert into events(start_time,end_time) values('2023-10-01 13:00:00','2023-10-01 13:45:00'); testdb_0903=> select id,start_time,end_time,extract(epoch from (end_time - start_time)) as diff_time from events; id | start_time | end_time | diff_time ----+---------------------+---------------------+----------- 1 | 2023-10-01 10:00:00 | 2023-10-01 10:30:00 | 1800 2 | 2023-10-01 11:00:00 | 2023-10-01 12:15:00 | 4500 3 | 2023-10-01 13:00:00 | 2023-10-01 13:45:00 | 2700 (3 rows) testdb_0903=> testdb_0903=> select id,start_time,end_time,trunc(extract(epoch from (end_time - start_time))) as diff_time from events; id | start_time | end_time | diff_time ----+---------------------+---------------------+----------- 1 | 2023-10-01 10:00:00 | 2023-10-01 10:30:00 | 1800 2 | 2023-10-01 11:00:00 | 2023-10-01 12:15:00 | 4500 3 | 2023-10-01 13:00:00 | 2023-10-01 13:45:00 | 2700 (3 rows)
資料型別轉化
dec(RATE,10,2) 函式轉換***************************************************************************** create table sales(id serial primary key,rate float); insert into sales(rate) values(123.456),(789.123456),(456.789),(12.3),(0.987654); testdb_0903=> select id,rate,rate::numeric(10,2) as formatted_rate from sales; id | rate | formatted_rate ----+---------------------+---------------- 1 | 123.456000000000003 | 123.46 2 | 789.123456000000033 | 789.12 3 | 456.788999999999987 | 456.79 4 | 12.3000000000000007 | 12.30 5 | .987654000000000032 | .99 (5 rows) testdb_0903=> testdb_0903=> testdb_0903=> select id,rate,cast(rate as numeric(10,2)) as formatted_rate from sales; id | rate | formatted_rate ----+---------------------+---------------- 1 | 123.456000000000003 | 123.46 2 | 789.123456000000033 | 789.12 3 | 456.788999999999987 | 456.79 4 | 12.3000000000000007 | 12.30 5 | .987654000000000032 | .99 (5 rows) int(100.123) 函式轉換***************************************************************************** testdb_0903=> select cast(100.123 as int) as int_value from dual; int_value ----------- 100 (1 row) testdb_0903=> testdb_0903=> select 100.123::int as int_values from dual; int_values ------------ 100 (1 row) char(1) 函式轉換***************************************************************************** testdb_0903=> select cast(1 as char(1)) as char_value; char_value ------------ 1 (1 row) to_char(1) 函式轉換***************************************************************************** testdb_0903=> select to_char(current_timestamp,'HH12:MI:SS'); to_char ---------- 10:14:46 (1 row) testdb_0903=> select to_char(125.8::real,'999D99'); to_char --------- 125.80 (1 row) to_date(1) 函式轉換***************************************************************************** testdb_0903=> select to_date('2024-09-30'); to_date --------------------- 2024-09-30 00:00:00 (1 row) testdb_0903=> select to_date('05 Dec 2000','DD Mon YYYY'); to_date --------------------- 2000-12-05 00:00:00 (1 row)
其它常用函式轉換
locate('|','115|56',1) 函式轉換***************************************************************************** testdb_0903=> select instr('115|56','|',1) as position from dual; position ---------- 4 (1 row) listagg(name,',') 函式轉換***************************************************************************** testdb_0903=> create table listagg_t1(a int,b text); testdb_0903=> insert into listagg_t1 values(null,'a1'),(1,'b2'),(1,'c3'),(2,'d4'),(2,'e5'),(3,'f6'); testdb_0903=> select a,listagg(b,';') within group (order by b) from listagg_t1 group by 1; a | listagg ---+--------- 1 | b2;c3 2 | d4;e5 3 | f6 | a1 (4 rows) nvl() 函式轉換***************************************************************************** testdb_0903=> select nvl(null,1); nvl ----- 1 (1 row) testdb_0903=> testdb_0903=> select nvl('Hello world',1); nvl ------------- Hello world (1 row) trim() 函式轉換***************************************************************************** testdb_0903=> select trim(BOTH 'x' from 'xTommxx'); btrim ------- Tomm (1 row) testdb_0903=> select trim(leading 'x' from 'xTommxx'); ltrim -------- Tommxx (1 row) testdb_0903=> select trim(trailing 'x' from 'xTommxx'); rtrim ------- xTomm (1 row) substr() 函式轉換***************************************************************************** testdb_0903=> testdb_0903=> select substr('stringtest' from 4); substr --------- ingtest (1 row) testdb_0903=> select substr('stringtest',4); substr --------- ingtest (1 row) testdb_0903=> testdb_0903=> select substr('teststring' from 5 for 2); substr -------- st (1 row) testdb_0903=> select substr('string',2,3); substr -------- tri (1 row) value() 函式轉換***************************************************************************** testdb_0903=> select coalesce(null,'hello'); coalesce ---------- hello (1 row) floor() 函式轉換***************************************************************************** testdb_0903=> select floor(-42.8); floor ------- -43 (1 row) round() 函式轉換***************************************************************************** testdb_0903=> select round(42.4); round ------- 42 (1 row) coalesce() 函式轉換***************************************************************************** testdb_0903=> select coalesce(null,'hello'); coalesce ---------- hello (1 row) length() 函式轉換***************************************************************************** testdb_0903=> select length('abcd'); length -------- 4 (1 row) testdb_0903=> testdb_0903=> select length('漢字abc'); length -------- 5 (1 row) posstr(exp1,exp2) 函式轉換***************************************************************************** testdb_0903=> select position('World' in 'Hello World') as pos; pos ----- 7 (1 row) replace() 函式轉換***************************************************************************** testdb_0903=> select replace('abcdefabcdef','cd','XXX'); replace ---------------- abXXXefabXXXef (1 row) testdb_0903=> select replace('abcdefabcdef','cd'); replace ---------- abefabef (1 row)