Hey Everyone,
I was hoping someone could give me a push in the right direction. From
the info on the web and what I've been reading in the manuals, and what
I've been trying out, I'm getting very confused.
What I'm trying to do is get the difference between two dates either in
minutes, hours, days, months or years.
Now if I have two timestamps I can subtract to two and I get an
interval. With extract I can get some useful information from the
interval but not useful enough since the interval seems to forget some
crucial data.
Say I have two timestamps: ' 2009-01-08 13:11:20' and '2009-03-22 11:55:10'
If I subtract the two I get an interval of '72 days 22:42:20',
interestingly the manual states I should be getting '2 months 14 days
22:52:20'
Neither help me much. With the result I'm getting right now I can easily
extract the number of days and get 72. If I want the number of hours I
would need to extract the number of days, multiply it by 24, and then
add the extract of hours. If I want minutes I would have to do 3
extracts. There has got to be an easier way..
If I want the number of months it would be more difficult, how many
months are there in 72 days? I can't simply divide by 30 or 31, February
only has 28 days..
If I would have gotten the result I was supposed to get according to the
manual, months would have been easy (though I would also need to extract
years and multiply it by 12). However days would be very difficult. How
many days are there in 2 months? That can only be answered if you know
what period you are talking about.
It really seems you need to do some very difficult stuff to do something
simple. Obviously Postgres can do exactly what I want or it would not be
able to calculate the interval value to begin with. Its just that I
can't extract the information I need from the interval value...
So how do I do what I need to do?
--
Greetz,
Bastiaan Olij
e-mail/MSN: bastiaan@basenlily.nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij
