Skip site navigation (1) Skip section navigation (2)

Difference between dates

From: Bastiaan Olij <lists(at)basenlily(dot)nl>
To: pgsql Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Difference between dates
Date: 2008-12-23 23:02:06
Message-ID: 49516DEE.8000506@basenlily.nl (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)basenlily(dot)nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2008-12-24 16:11:14
Subject: Re: Difference between dates
Previous:From: Ognjen BlagojevicDate: 2008-12-23 12:13:17
Subject: Re: Fw: Re: connection refused

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group