Native SQL functions

Content

The latest version of FP uses Trino as the query engine, which imply slight changes in the queries' statements.

Known functions(frequent used) change:

PreviousCurrentExample
current_date()current_date-
current_timestamp()current_timestamp-
timestamp('xxxx-xx-xx')timestamp'xxxx-xx-xx'-
date_add (date,INTERVAL expr type)date_add(unit, value, timestamp)Change
date_add(current_date(),INTERVAL 1 day)
to
date_add('day',1,current_date)
datetime_add(datetime,INTERVAL expr type)date_add(unit, value, timestamp)-
timestamp_add(datetime,INTERVAL expr type)date_add(unit, value, timestamp)-
date_sub(date,INTERVAL expr type)date_add(unit, value, timestamp)Change
date_sub(current_date(),INTERVAL 1 day)
to
date_add('day',-1,current_date)
timestamp_sub(datetime,INTERVAL expr type)date_add(unit, value, timestamp)-
datediff(date1,date2)date_diff(unit, date1, date2)datediff(date1,date2)
Change to
date_diff('day',date2,date1)
field=""field=''Change
select \* from xx where field1="xy"
to
select \* from xx where field1='xy'
/Time fields cannot be compared to stringsChange
on_date >= '2021-12-01'
to
on_date >= date'2021-12-01'
or
on_date >= timestamp'2021-12-01'
group by aliasgroup by originChange
select on_date as "day",count(1)as cnt from xx group by day
to
select on_date as "day",count(1)as cnt from xx group by on_date
having aliashaving originChange
select on_date as "day",count(1)as cnt from xx group by on_date having cnt>100
to
select on_date as "day",count(1)as cnt from xx group by on_date having count(1)>100
ifnull(expr1, expr2)coalesce(expr1, expr2)-
str_to_date()date_parse()-
floatdouble or real-
stringvarchar-
lag(expr, offset, default)lag(expr, offset)-
lead(expr, offset, default)lead(expr, offset)-