pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: DateAdd function ? 2+ messages / 2 participants [nested] [flat]
* Re: DateAdd function ? @ 2005-04-19 14:23 Jeff Eckermann <jeff_eckermann@yahoo.com> 2005-04-19 14:37 ` Re: DateAdd function ? Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Jeff Eckermann @ 2005-04-19 14:23 UTC (permalink / raw) To: pgsql-sql "Zlatko Matiæ" <zlatko.matic1@sb.t-com.hr> wrote in message news:d42nl1$7al$1@ss405.t-com.hr... >I am currently migrating from MSDE to PostgreSQL and have to rewrite the >function that is calculating next date of sampling... > In MSDE there is a DateAdd function. I can't find the appropriate function > in postgre. Can you help me? There is no "dateadd" function in PostgreSQL, but you can write your own easily enough. The following should give you an idea of the logic you can use: jeck=# select current_date; date ------------ 2005-04-19 (1 row) jeck=# select current_date + cast('1 day' as interval); ?column? ------------------------ 2005-04-20 00:00:00-05 (1 row) jeck=# select current_date + cast('3 months' as interval); ?column? ------------------------ 2005-07-19 00:00:00-05 (1 row) jeck=# select current_date + 3 * cast('1 month' as interval); ?column? ------------------------ 2005-07-19 00:00:00-05 (1 row) > The function in MSDE is the following: > > ALTER FUNCTION dbo.slisp > ( > @UCESTALOST_BROJ int, > @UCESTALOST_JEDINICA nvarchar (50), > @DATUM_ISPITIVANJA datetime > ) > RETURNS datetime > AS > BEGIN > DECLARE @SLISP datetime > IF @UCESTALOST_JEDINICA='m' SET @SLISP=DATEADD(month, @UCESTALOST_BROJ, > MAX(@DATUM_ISPITIVANJA)) > IF @UCESTALOST_JEDINICA='g' SET @SLISP=DATEADD(year, @UCESTALOST_BROJ, > MAX(@DATUM_ISPITIVANJA)) > IF @UCESTALOST_JEDINICA='d' SET @SLISP=DATEADD(day, @UCESTALOST_BROJ, > MAX(@DATUM_ISPITIVANJA)) > RETURN @SLISP > END > > Thanks. > ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: DateAdd function ? 2005-04-19 14:23 Re: DateAdd function ? Jeff Eckermann <jeff_eckermann@yahoo.com> @ 2005-04-19 14:37 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2005-04-19 14:37 UTC (permalink / raw) To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql "Jeff Eckermann" <jeff_eckermann@yahoo.com> writes: > There is no "dateadd" function in PostgreSQL, but you can write your own > easily enough. The following should give you an idea of the logic you can > use: > jeck=# select current_date + cast('1 day' as interval); Alternatively, maybe you want to use the date-plus/minus-integer operators. regression=# select current_date; date ------------ 2005-04-19 (1 row) regression=# select current_date + 1; ?column? ------------ 2005-04-20 (1 row) regression=# select current_date - 1; ?column? ------------ 2005-04-18 (1 row) There isn't really a date-plus-interval operator --- what's happening there is the date is implicitly promoted to timestamp and then timestamp-plus-interval is used. You might want this if you want to add symbolic quantities like '2 months' or '1 year', but for small integer numbers of days, the integer operations are faster and simpler to use. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-19 14:37 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-04-19 14:23 Re: DateAdd function ? Jeff Eckermann <jeff_eckermann@yahoo.com> 2005-04-19 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox