MSSQL
select dateadd(year,1, '2004-2-29')
PostgreSQL
select '2004-2-29'::date + cast('1 years' as interval)
MSSQL:
select dateadd(month,1,'2004-2-29')
PostgreSQL:
select '2004-2-29'::date + cast('1 months' as interval)
MSSQL:
select dateadd(month,1, '1976-11-05 6:29 AM')
PostgreSQL:
select '2006-01-06 6:29 AM'::timestamptz + cast('1 months' as interval)
MSSQL:
select dateadd(day,1,'2006-01-06 6:29 AM')
PosgtgreSQL:
select '2006-01-06 6:29 AM'::timestamp + cast('1 days' as interval);
在Postgresql中创建dateadd function:
CREATE OR REPLACE FUNCTION dateadd(
character varying,
integer,
timestamp without time zone)
RETURNS timestamp without time zone AS
$BODY$
DECLARE
p_Interval ALIAS FOR $1;
p_N ALIAS FOR $2;
p_Date ALIAS FOR $3;
BEGIN
if p_Interval = 's' or p_Interval='ss' then
return p_Date + p_N*interval'1 second';
elseif p_Interval = 'm' then
return p_Date + cast(p_N || ' months' as interval);
elseif p_Interval = 'y' then
return p_Date + cast(p_N || ' years' as interval);
else
raise exception 'dateadd interval parameter not supported';
-- raise exception 'hello';
return null;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;