pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Date 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