Thread: Date manipulation
How does one perform date manipulation within SQL? For example, SQL Server has a dateadd() function that takes a date part, scalar, and the date to manipulate. I have a query that determines the number of days that are between now and a particular date that looks something like this: select datediff (dd, now (), column) as difference_in_days from ... Anything available short of coding my own function? Thanks, Mark
Mark writes: > I have a query that determines the number of days that are between now > and a particular date that looks something like this: > > select datediff (dd, now (), column) as difference_in_days from ... > > Anything available short of coding my own function? select date 'particular date' - current_date; -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Mark,
> How does one perform date manipulation within SQL? For example, SQL
> Server has a dateadd() function that takes a date part, scalar, and
> the
> date to manipulate.
As I have remarked before, such functions as DATEADD are unnecessary in
PostgreSQL because PostgreSQL has a proper implementation of Date data
types, unlike MS SQL Server. Thus, to add to a date:
new_date := old_date + INTERVAL('1 week');
Or to subtract:
break_time := restart_time - stop_time;
It's improtant to remeber that the differnence of two dates or times is
an interval, and while you can add an interval to a date you cannot add
two dates.
Additionally, if you browse to Roberto Mello's PG/plSQL function library
(see link for the PostgreSQL.org web site) you will find an extension to
the OVERLAPS function that I find quite useful (I should, I wrote it!).
-Josh
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 31 May 2001 17:24:54 +0000 (UTC), mark@zserve.com (Mark) wrote: > >How does one perform date manipulation within SQL? For example, SQL >Server has a dateadd() function that takes a date part, scalar, and the >date to manipulate. > >I have a query that determines the number of days that are between now >and a particular date that looks something like this: > >select datediff (dd, now (), column) as difference_in_days from ... > >Anything available short of coding my own function? Yes: see http://www.postgresql.bit.nl/devel-corner/docs/user/functions-formatting.html and http://www.postgresql.bit.nl/devel-corner/docs/user/functions-datetime.html -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud@xs4all.nl http://www.xs4all.nl/~reinoud __________________________________________________