Обсуждение: subtract two dates to get the number of days
<div class="WordSection1"><p class="MsoNormal">I want to subtract to dates to know the number of days different.<p class="MsoNormal"> <pclass="MsoNormal">Example:<p class="MsoNormal">01/02/2010 - 01/01/2010 = 1 day<p class="MsoNormal">08/01/2010- 07/31/2010 = 1 day<p class="MsoNormal"> <p class="MsoNormal">How do I do this? <p class="MsoNormal"> <pclass="MsoNormal">Thanks,<p class="MsoNormal"> <p class="MsoNormal">Lance Campbell<p class="MsoNormal">SoftwareArchitect/DBA/Project Manager<p class="MsoNormal">Web Services at Public Affairs<p class="MsoNormal">217-333-0382<pclass="MsoNormal"> </div>
"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
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';
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 :-)
-----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-----
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
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
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