pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubtract two dates to get the number of days
8+ messages / 5 participants
[nested] [flat]
* subtract two dates to get the number of days
@ 2010-07-13 14:58 Campbell, Lance <lance@illinois.edu>
0 siblings, 4 replies; 8+ messages in thread
From: Campbell, Lance @ 2010-07-13 14:58 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
Lance Campbell
Software Architect/DBA/Project Manager
Web Services at Public Affairs
217-333-0382
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-13 15:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Campbell, Lance <lance@illinois.edu>
3 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2010-07-13 15:02 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-13 15:05 Thomas Kellerer <spam_eater@gmx.net>
parent: Campbell, Lance <lance@illinois.edu>
3 siblings, 0 replies; 8+ messages in thread
From: Thomas Kellerer @ 2010-07-13 15:05 UTC (permalink / raw)
To: pgsql-sql
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';
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-14 05:28 silly sad <sad@bankir.ru>
parent: Campbell, Lance <lance@illinois.edu>
3 siblings, 0 replies; 8+ messages in thread
From: silly sad @ 2010-07-14 05:28 UTC (permalink / raw)
To: pgsql-sql
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 :-)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-14 12:37 Jean-David Beyer <jeandavid8@verizon.net>
parent: Campbell, Lance <lance@illinois.edu>
3 siblings, 1 reply; 8+ messages in thread
From: Jean-David Beyer @ 2010-07-14 12:37 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-14 16:06 Thomas Kellerer <spam_eater@gmx.net>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2010-07-14 16:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-14 17:05 Jean-David Beyer <jeandavid8@verizon.net>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Jean-David Beyer @ 2010-07-14 17:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: subtract two dates to get the number of days
@ 2010-07-15 07:18 Thomas Kellerer <spam_eater@gmx.net>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas Kellerer @ 2010-07-15 07:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-07-15 07:18 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-13 14:58 subtract two dates to get the number of days Campbell, Lance <lance@illinois.edu>
2010-07-13 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-13 15:05 ` Thomas Kellerer <spam_eater@gmx.net>
2010-07-14 05:28 ` silly sad <sad@bankir.ru>
2010-07-14 12:37 ` Jean-David Beyer <jeandavid8@verizon.net>
2010-07-14 16:06 ` Thomas Kellerer <spam_eater@gmx.net>
2010-07-14 17:05 ` Jean-David Beyer <jeandavid8@verizon.net>
2010-07-15 07:18 ` Thomas Kellerer <spam_eater@gmx.net>
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