From lance@illinois.edu Tue Jul  13 14:59:02 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 9346163291E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 11:59:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 26472-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 14:58:54 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sab-fillmore.sab.uiuc.edu (swan16.admin.uiuc.edu
	[128.174.254.16])
	by mail.postgresql.org (Postfix) with ESMTP id 331046327AE
	for <pgsql-sql@postgresql.org>; Tue, 13 Jul 2010 11:58:55 -0300 (ADT)
Received: from SAB-FENWICK.sab.uiuc.edu ([128.174.254.19]) by
	sab-fillmore.sab.uiuc.edu with Microsoft SMTPSVC(6.0.3790.4675);
	Tue, 13 Jul 2010 09:58:53 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01CB229B.E904809A"
Subject: subtract two dates to get the number of days
Date: Tue, 13 Jul 2010 09:58:53 -0500
Message-ID: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: subtract two dates to get the number of days
Thread-Index: Acsim+lWyXVwD7HQTfGLG907lxfB8Q==
From: "Campbell, Lance" <lance@illinois.edu>
To: <pgsql-sql@postgresql.org>
X-OriginalArrivalTime: 13 Jul 2010 14:58:53.0475 (UTC)
	FILETIME=[E902EF30:01CB229B]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.101 tagged_above=-5 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201007/57
X-Sequence-Number: 34370

This is a multi-part message in MIME format.

------_=_NextPart_001_01CB229B.E904809A
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I want to subtract to dates to know the number of days different.

=20

Example:

01/02/2010 - 01/01/2010 =3D 1 day

08/01/2010 - 07/31/2010 =3D 1 day

=20

How do I do this?=20

=20

Thanks,

=20

Lance Campbell

Software Architect/DBA/Project Manager

Web Services at Public Affairs

217-333-0382

=20


------_=_NextPart_001_01CB229B.E904809A
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DWordSection1>

<p class=3DMsoNormal>I want to subtract to dates to know the number of =
days
different.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Example:<o:p></o:p></p>

<p class=3DMsoNormal>01/02/2010 - 01/01/2010 =3D 1 day<o:p></o:p></p>

<p class=3DMsoNormal>08/01/2010 - 07/31/2010 =3D 1 day<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>How do I do this? <o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Lance Campbell<o:p></o:p></p>

<p class=3DMsoNormal>Software Architect/DBA/Project =
Manager<o:p></o:p></p>

<p class=3DMsoNormal>Web Services at Public Affairs<o:p></o:p></p>

<p class=3DMsoNormal>217-333-0382<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

</div>

</body>

</html>

------_=_NextPart_001_01CB229B.E904809A--



From tgl@sss.pgh.pa.us Tue Jul  13 15:02:28 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 76E2663291E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 12:02:28 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 45271-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 15:02:21 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id A7A84632DD7
	for <pgsql-sql@postgresql.org>; Tue, 13 Jul 2010 12:02:20 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o6DF2APD029027;
	Tue, 13 Jul 2010 11:02:10 -0400 (EDT)
To: "Campbell, Lance" <lance@illinois.edu>
cc: pgsql-sql@postgresql.org
Subject: Re: subtract two dates to get the number of days 
In-reply-to: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
Comments: In-reply-to "Campbell, Lance" <lance@illinois.edu>
	message dated "Tue, 13 Jul 2010 09:58:53 -0500"
Date: Tue, 13 Jul 2010 11:02:10 -0400
Message-ID: <29026.1279033330@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/58
X-Sequence-Number: 34371

"Campbell, Lance" <lance@illinois.edu> writes:
> I want to subtract to dates to know the number of days different.
> Example:
> 01/02/2010 - 01/01/2010 = 1 day
> 08/01/2010 - 07/31/2010 = 1 day
> How do I do this? 

If they're dates, you just subtract.

regression=# select '08/01/2010'::date - '07/31/2010'::date;
 ?column? 
----------
        1
(1 row)


			regards, tom lane



From spam_eater@gmx.net Tue Jul  13 15:05:38 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 85F84632975
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 12:05:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 33558-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 13 Jul 2010 15:05:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 16C4563291E
	for <pgsql-sql@postgresql.org>; Tue, 13 Jul 2010 12:05:26 -0300 (ADT)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1OYh2x-0005um-Rd
	for pgsql-sql@postgresql.org; Tue, 13 Jul 2010 17:05:23 +0200
Received: from 217.110.94.67 ([217.110.94.67])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 13 Jul 2010 17:05:23 +0200
Received: from spam_eater by 217.110.94.67 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 13 Jul 2010 17:05:23 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: subtract two dates to get the number of days
Date: Tue, 13 Jul 2010 17:05:12 +0200
Lines: 18
Message-ID: <i1hvb8$58a$1@dough.gmane.org>
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: 217.110.94.67
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.954 tagged_above=-5 required=5 tests=BAYES_50=0.8,
	RCVD_NUMERIC_HELO=1.164, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: *
X-Archive-Number: 201007/59
X-Sequence-Number: 34372

Campbell, Lance, 13.07.2010 16:58:
> I want to subtract to dates to know the number of days different.
>
> Example:
>
> 01/02/2010 - 01/01/2010 = 1 day
>
> 08/01/2010 - 07/31/2010 = 1 day
>
> How do I do this?

SELECT DATE '2010-02-01' - DATE '2010-01-01';

SELECT DATE '2010-08-01' - DATE '2010-07-31';








From sad@bankir.ru Wed Jul  14 05:28:47 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 279FF632975
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 02:28:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 99167-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 05:28:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moon.antora.ru (moon.antora.ru [89.111.189.180])
	by mail.postgresql.org (Postfix) with ESMTP id 083CA63282B
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 02:28:39 -0300 (ADT)
Envelope-to: pgsql-sql@postgresql.org
Received: from moon.inner.space ([10.1.1.5] helo=moon.antora.ru)
	by moon.antora.ru with esmtp (Exim 4.67)
	(envelope-from <sad@bankir.ru>) id 1OYuWK-0006Rz-7L
	for pgsql-sql@postgresql.org; Wed, 14 Jul 2010 09:28:36 +0400
Received: from [192.168.1.6] [85.172.41.139] by moon.antora.ru (Axigen)
	with (DHE-RSA-AES256-SHA encrypted) ESMTPSA id 1E515A;
	Wed, 14 Jul 2010 09:28:35 +0400
Message-ID: <4C3D4B03.5070009@bankir.ru>
Date: Wed, 14 Jul 2010 09:28:35 +0400
From: silly sad <sad@bankir.ru>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.5)
	Gecko/20100111 Thunderbird/3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: subtract two dates to get the number of days
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
In-Reply-To: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.8 tagged_above=-5 required=5 tests=BAYES_50=0.8
X-Spam-Level: 
X-Archive-Number: 201007/60
X-Sequence-Number: 34373

On 07/13/10 18:58, Campbell, Lance wrote:
> I want to subtract to dates to know the number of days different.

it would be far more interesting operation if u want to divide an 
INTERVAL by INTERVAL to get a ratio :-)



From jeandavid8@verizon.net Wed Jul  14 12:37:27 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C4C4C632B68
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 09:37:27 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 74703-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 12:37:20 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from vms173005pub.verizon.net (vms173005pub.verizon.net
	[206.46.173.5])
	by mail.postgresql.org (Postfix) with ESMTP id 2649063291E
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 09:37:20 -0300 (ADT)
Received: from trillian.localdomain ([unknown] [96.240.12.15])
	by vms173005.mailsrvcs.net
	(Sun Java(tm) System Messaging Server 7u2-7.02 32bit (built Apr 16
	2009)) with ESMTPA id <0L5J00IXSSDVIY70@vms173005.mailsrvcs.net> for
	pgsql-sql@postgresql.org; Wed, 14 Jul 2010 07:37:07 -0500 (CDT)
Message-id: <4C3DAF72.6030606@verizon.net>
Date: Wed, 14 Jul 2010 08:37:06 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Organization: Institute for Regimented Whimsey
User-Agent: Thunderbird 2.0.0.24 (X11/20100318)
MIME-version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: subtract two dates to get the number of days
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
In-reply-to: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
X-Enigmail-Version: 0.96.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/63
X-Sequence-Number: 34376

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Campbell, Lance wrote:
> I want to subtract to dates to know the number of days different.
> 
> 
> 
> Example:
> 
> 01/02/2010 - 01/01/2010 = 1 day
> 
> 08/01/2010 - 07/31/2010 = 1 day
> 
> 
> 
> How do I do this?
> 
Others have posted SQL answers to this. Which is fine if you need to do
it in SQL.

My dates are of the form yyyy-mm-dd and such. And I want to do things
like adding or subtracting days, months, or years to it or from it. Also
the logical comparisons.

Years ago, I made a C++ data type that allowed a date datatype where I
could add, subtract, and so on.

I use it in programs that do not necessarily use a database, but also in
programs that do when the computations are the big part of the cpu load,
as contrasted to just "gentle" massaging of existing data.


- --
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 08:20:01 up 6 days, 17:06, 4 users, load average: 4.77, 4.78, 4.87
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org/

iD8DBQFMPa9yPtu2XpovyZoRAnuDAJ9U9yghDl8NkGNv1pWSxIwXsDBTXwCgiv1L
INK1dzbUQnWBjhXXrQu6ZsM=
=lyR9
-----END PGP SIGNATURE-----



From spam_eater@gmx.net Wed Jul  14 16:06:41 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id AE227632E8E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 13:06:41 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 99864-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 16:06:34 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 91B796327AE
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 13:06:34 -0300 (ADT)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1OZ4Te-00007i-BD
	for pgsql-sql@postgresql.org; Wed, 14 Jul 2010 18:06:30 +0200
Received: from ppp-188-174-2-78.dynamic.mnet-online.de ([188.174.2.78])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 18:06:30 +0200
Received: from spam_eater by ppp-188-174-2-78.dynamic.mnet-online.de with
	local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 18:06:30 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: subtract two dates to get the number of days
Date: Wed, 14 Jul 2010 18:06:22 +0200
Lines: 19
Message-ID: <i1kn9t$9e5$1@dough.gmane.org>
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
	<4C3DAF72.6030606@verizon.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: ppp-188-174-2-78.dynamic.mnet-online.de
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <4C3DAF72.6030606@verizon.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.011 tagged_above=-5 required=5
	tests=BAYES_40=-0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/66
X-Sequence-Number: 34379

Jean-David Beyer wrote on 14.07.2010 14:37:
> My dates are of the form yyyy-mm-dd and such.
Storing a date as a string is never a good idea.

> And I want to do things like adding or subtracting days, months, or years to it or from it.
>Also the logical comparisons.
Which is all a piece of cake when you use the proper datatype
  
> Years ago, I made a C++ data type that allowed a date datatype where I
> could add, subtract, and so on.
> I use it in programs that do not necessarily use a database,
To be honest: I expect the programming language to support those things.

> but also in programs that do when the computations are the big part of the cpu load,
> as contrasted to just "gentle" massaging of existing data.
I would expect doing "date maths" with strings is wasting more CPU than using a native date datatype.

Just my €0.02
Thomas




From jeandavid8@verizon.net Wed Jul  14 17:05:35 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 0EE44632AF0
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 14:05:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 06553-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 14 Jul 2010 17:05:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from vms173003pub.verizon.net (vms173003pub.verizon.net
	[206.46.173.3])
	by mail.postgresql.org (Postfix) with ESMTP id F0D29632768
	for <pgsql-sql@postgresql.org>; Wed, 14 Jul 2010 14:05:27 -0300 (ADT)
MIME-version: 1.0
Content-transfer-encoding: 8BIT
Content-type: text/plain; charset=windows-1252
Received: from trillian.localdomain ([unknown] [96.240.12.15])
	by vms173003.mailsrvcs.net
	(Sun Java(tm) System Messaging Server 7u2-7.02 32bit (built Apr 16
	2009)) with ESMTPA id <0L5K00EUZ4SG38E0@vms173003.mailsrvcs.net> for
	pgsql-sql@postgresql.org; Wed, 14 Jul 2010 12:05:05 -0500 (CDT)
Message-id: <4C3DEE40.1040208@verizon.net>
Date: Wed, 14 Jul 2010 13:05:04 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Organization: Institute for Regimented Whimsey
User-Agent: Thunderbird 2.0.0.24 (X11/20100318)
To: pgsql-sql@postgresql.org
Subject: Re: subtract two dates to get the number of days
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
	<4C3DAF72.6030606@verizon.net> <i1kn9t$9e5$1@dough.gmane.org>
In-reply-to: <i1kn9t$9e5$1@dough.gmane.org>
X-Enigmail-Version: 0.96.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.011 tagged_above=-5 required=5
	tests=BAYES_40=-0.001, RCVD_IN_DNSWL_NONE=-0.0001,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/69
X-Sequence-Number: 34382

Thomas Kellerer wrote:
> Jean-David Beyer wrote on 14.07.2010 14:37:
>> My dates are of the form yyyy-mm-dd and such.
> Storing a date as a string is never a good idea.

I started this long ago, when postgreSQL did not really work very well
(1998?). One version of it would not do views, and another had trouble
with primary keys, IIRC. So I first used Informix, until it would not
work any more. It did not really support embedded SQL in C++, only in C,
so that was a pain. But it quit working when Red Hat updated from
release 5 to release 6.

I then moved to IBM's DB2, and that worked very well, but it got too
expensive to keep it when I went from one release of my OS to another
for just my own workstation use. Somewhere around 2004, or a little
before, I decided to give postgreSQL another chance, and it works just fine.


I just looked them up in my data definitions. Dates are _stored_ as type

DATE NOT NULL

so I store them OK. It is just when I want to compute with them that it
gets a bit tricky. Or it did way back when I wrote that stuff in the
late 1990s.
> 
>> And I want to do things like adding or subtracting days, months, or years to it or from it.
>> Also the logical comparisons.
> Which is all a piece of cake when you use the proper datatype

Yes, if the data happen to be stored at all. But when a program
generates the dates dynamically and wants to produce queries from them,
it is easier to use the C++ class to generate the dates.
>   
>> Years ago, I made a C++ data type that allowed a date datatype where I
>> could add, subtract, and so on.
>> I use it in programs that do not necessarily use a database,

> To be honest: I expect the programming language to support those things.

I would love it. For all I know, the C++ Standard Library supports it
now, but I do not believe it did when I wrote that class.
> 
>> but also in programs that do when the computations are the big part of the cpu load,
>> as contrasted to just "gentle" massaging of existing data.
> I would expect doing "date maths" with strings is wasting more CPU than using a native date datatype.

My class pretty much does not do it as strings, but as integers (internally)
> 
> Just my €0.02
> Thomas
> 
Well, €0.02 is still more than my US$0.02, I believe.


-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 12:45:01 up 6 days, 21:31, 4 users, load average: 4.65, 4.69, 4.71



From spam_eater@gmx.net Thu Jul  15 07:18:49 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id AC15A632668
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 15 Jul 2010 04:18:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 25729-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 15 Jul 2010 07:18:42 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 9D4BF62F6AC
	for <pgsql-sql@postgresql.org>; Thu, 15 Jul 2010 04:18:42 -0300 (ADT)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1OZIiO-00073k-SI
	for pgsql-sql@postgresql.org; Thu, 15 Jul 2010 09:18:40 +0200
Received: from 217.110.94.67 ([217.110.94.67])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 15 Jul 2010 09:18:40 +0200
Received: from spam_eater by 217.110.94.67 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 15 Jul 2010 09:18:40 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: subtract two dates to get the number of days
Date: Thu, 15 Jul 2010 09:18:29 +0200
Lines: 30
Message-ID: <i1mco5$3fk$1@dough.gmane.org>
References: 
 <B10E6810AC2A2F4EA7550D072CDE876004B2E65D@SAB-FENWICK.sab.uiuc.edu>
	<4C3DAF72.6030606@verizon.net> <i1kn9t$9e5$1@dough.gmane.org>
	<4C3DEE40.1040208@verizon.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: 217.110.94.67
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: <4C3DEE40.1040208@verizon.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.954 tagged_above=-5 required=5 tests=BAYES_50=0.8,
	RCVD_NUMERIC_HELO=1.164, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: *
X-Archive-Number: 201007/73
X-Sequence-Number: 34386

Jean-David Beyer, 14.07.2010 19:05:
> I just looked them up in my data definitions. Dates are _stored_ as type
>
> DATE NOT NULL
Very good ;)
  
> Yes, if the data happen to be stored at all. But when a program
> generates the dates dynamically and wants to produce queries from them,
> it is easier to use the C++ class to generate the dates.
Yes of course.

>>> Years ago, I made a C++ data type that allowed a date datatype where I
>>> could add, subtract, and so on.
>>> I use it in programs that do not necessarily use a database,
>
>> To be honest: I expect the programming language to support those things.
>
> I would love it. For all I know, the C++ Standard Library supports it
> now, but I do not believe it did when I wrote that class.
Ah, those "historic" things. I primarily use Java and that had Date support right from the start.

> Well, €0.02 is still more than my US$0.02, I believe.

Nice one :)


Regards
Thomas