pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate manipulation
7+ messages / 6 participants
[nested] [flat]
* Date manipulation
@ 2001-05-31 16:22 Mark <mark@zserve.com>
0 siblings, 3 replies; 7+ messages in thread
From: Mark @ 2001-05-31 16:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date manipulation
@ 2001-05-31 19:21 Reinoud van Leeuwen <reinoud@xs4all.nl>
parent: Mark <mark@zserve.com>
2 siblings, 0 replies; 7+ messages in thread
From: reinoud@xs4all.nl @ 2001-05-31 19:21 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date manipulation
@ 2001-05-31 21:08 Peter Eisentraut <peter_e@gmx.net>
parent: Mark <mark@zserve.com>
2 siblings, 0 replies; 7+ messages in thread
From: Peter Eisentraut @ 2001-05-31 21:08 UTC (permalink / raw)
To: Mark <mark@zserve.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date manipulation
@ 2001-06-01 16:21 Josh Berkus <josh@agliodbs.com>
parent: Mark <mark@zserve.com>
2 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2001-06-01 16:21 UTC (permalink / raw)
To: Mark <mark@zserve.com>; pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* date manipulation
@ 2002-04-03 20:15 Travis Hoyt <thoyt@npc.net>
0 siblings, 2 replies; 7+ messages in thread
From: Travis Hoyt @ 2002-04-03 20:15 UTC (permalink / raw)
To: pgsql-sql
Hello,
Curious to know if there's an easy way to do what I'm trying to do. As of
yet I haven't been able to find any documentation or examples that help.
I basically want to be able to figure how how many 1st's and 15th's there
are between date X and Y. Is there a simple solution?
Thanks,
Travis
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.0K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: date manipulation
@ 2002-04-03 21:14 Ewald Geschwinde <webmaster@geschwinde.net>
parent: Travis Hoyt <thoyt@npc.net>
1 sibling, 0 replies; 7+ messages in thread
From: Ewald Geschwinde @ 2002-04-03 21:14 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; +Cc: pgsql-sql
Travis Hoyt wrote:
>Hello,
>
>Curious to know if there's an easy way to do what I'm trying to do. As of
>yet I haven't been able to find any documentation or examples that help.
>I basically want to be able to figure how how many 1st's and 15th's there
>are between date X and Y. Is there a simple solution?
>
>Thanks,
>
>Travis
>
There is a good DATE/TIME FAQ from josh berkus at
http://techdocs.postgresql.org
Ewald
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: date manipulation
@ 2002-04-03 21:57 Josh Berkus <josh@agliodbs.com>
parent: Travis Hoyt <thoyt@npc.net>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2002-04-03 21:57 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; pgsql-sql
Travis,
> Curious to know if there's an easy way to do what I'm trying to do.
> As of
> yet I haven't been able to find any documentation or examples that
> help.
> I basically want to be able to figure how how many 1st's and 15th's
> there
> are between date X and Y. Is there a simple solution?
Please see the Time/Date/Interval FAQ:
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
and the documentation:
http://www.postgresql.org/idocs/index.php?datatype-datetime.html
Finally, Joe Celko's book "SQL For Smarties" has a question and
solution very similar to what you ask above.
-Josh Berkus
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-04-03 21:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-31 16:22 Date manipulation Mark <mark@zserve.com>
2001-05-31 19:21 ` Reinoud van Leeuwen <reinoud@xs4all.nl>
2001-05-31 21:08 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-01 16:21 ` Josh Berkus <josh@agliodbs.com>
2002-04-03 20:15 date manipulation Travis Hoyt <thoyt@npc.net>
2002-04-03 21:14 ` Re: date manipulation Ewald Geschwinde <webmaster@geschwinde.net>
2002-04-03 21:57 ` Re: date manipulation Josh Berkus <josh@agliodbs.com>
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