一、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 | 19372bob | 2023-02-20 00:00:00 | 19408david | 2023-04-05 00:00:00 | 19452eve | 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 | 19373bob | 2023-02-20 00:00:00 | 19409david | 2023-04-05 00:00:00 | 19453eve | 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 | 15bob | 2023-02-20 00:00:00 | 20david | 2023-04-05 00:00:00 | 5eve | 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 | 15bob | 2023-02-20 00:00:00 | 20david | 2023-04-05 00:00:00 | 5eve | 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 | 18002 | 2023-10-01 11:00:00 | 2023-10-01 12:15:00 | 45003 | 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 | 18002 | 2023-10-01 11:00:00 | 2023-10-01 12:15:00 | 45003 | 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.462 | 789.123456000000033 | 789.123 | 456.788999999999987 | 456.794 | 12.3000000000000007 | 12.305 | .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.462 | 789.123456000000033 | 789.123 | 456.788999999999987 | 456.794 | 12.3000000000000007 | 12.305 | .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;c32 | d4;e53 | 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)
