[SQL] Interval subtracting
Hi all, Is there something incorrect in the above query ? SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / Thanks a lot, Milorad Poluga --- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Interval subtracting
Milorad Poluga написа: Hi all, Is there something incorrect in the above query ? SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / How many days are there in a month? -- Milen A. Radev ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Interval subtracting
On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
> Milorad Poluga написа:
> > Hi all,
> >
> > Is there something incorrect in the above query ?
> > SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
> > days'::interval
> >
> > ?column?
> > ---
> > 3 mons -14 days
> >
> > Why not '2 mons 16 days' ?
> >
> > /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc
> > (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>
>
> How many days are there in a month?
>
I beleive that a month is calculated on the 30-days base.
One way to solve this problem is to use a neutal date element and make
timestamps :
SELECT age(('1990-01-01'::date + '10 years 1 mons 1 days'::interval)::timestamp
,
('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
age
--
2 mons 16 days
Regards,
Milorad Poluga
---
[EMAIL PROTECTED]
---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Interval subtracting
Milorad Poluga написа:
> On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
>> Milorad Poluga написа:
>>> Hi all,
>>>
>>> Is there something incorrect in the above query ?
>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
>>> days'::interval
>>>
>>> ?column?
>>> ---
>>> 3 mons -14 days
>>>
>>> Why not '2 mons 16 days' ?
>>>
>>> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc
>>> (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>>
>> How many days are there in a month?
>>
>
> I beleive that a month is calculated on the 30-days base.
Are you sure? Where?
>
> One way to solve this problem is to use a neutal date element and make
> timestamps :
>
> SELECT age(('1990-01-01'::date + '10 years 1 mons 1
> days'::interval)::timestamp ,
> ('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
>
> age
> --
> 2 mons 16 days
>
Please read the last paragraph in section 8.5.1.4 of the manual
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
. It mentions the functions named "justify_days" and "justify_hours"
that could do what you need.
--
Milen A. Radev
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Need help: Find dirty rows, Update, Delete SQL
Hello, I need a bit of help with some SQL. I have two tables, call them Page and Bookmark. Each row in Page can have many Bookmarks pointing to it, and they are joined via a FK (Page.id = Bookmark.page_id). Page has a 'url' column: Page.url, which has a unique index on it. My Page.url column got a little dirty, and I need to clean it up, and that's what I need help with. Here is an example of dirtiness: Page: id=1 url = 'http://example.com/' id=2 url = 'http://example.com/#' -- dirty id=3 url = 'http://example.com/#foo' -- dirty The last two rows are dirty. Normally I normalize URLs before inserting them, but these got in, and now I need to clean them. The problem is that rows in Bookmark table may point to dirty rows in Page, so I can't just remove the dirty rows, and I can't just update 'url' column in Page to 'http://example.com/', because that column is unique. Is there some fancy SQL that I can use them to find the dirty rows in page (... where url like '%#%') and then find rows in Bookmark table that point to them, then point those rows to good rows in Page (e.g. id=1 row above), and finally remove the dirty rows from Page? Any help would be greatly appreciated. I'm using Pg 8.0.3 Thanks, Otis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Interval subtracting
"Milen A. Radev" <[EMAIL PROTECTED]> writes: > Milorad Poluga напиÑа: >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 >>> days'::interval >>> ?column? >>> --- >>> 3 mons -14 days >>> >>> Why not '2 mons 16 days' ? > Please read the last paragraph in section 8.5.1.4 of the manual > (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) > . It mentions the functions named "justify_days" and "justify_hours" > that could do what you need. justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are >= 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 <= days < 30, not merely days < 30. Similarly for justify_hours. Comments anyone? Patch anyone? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Interval subtracting
On Sat, 18 Feb 2006, Tom Lane wrote: > "Milen A. Radev" <[EMAIL PROTECTED]> writes: > > Milorad Poluga : > >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 > >>> days'::interval > >>> ?column? > >>> --- > >>> 3 mons -14 days > >>> > >>> Why not '2 mons 16 days' ? > > > Please read the last paragraph in section 8.5.1.4 of the manual > > (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) > > . It mentions the functions named "justify_days" and "justify_hours" > > that could do what you need. > > justify_days doesn't currently do anything with this result --- it > thinks its charter is only to reduce day components that are >= 30 days. > However, I think a good case could be made that it should normalize > negative days too; that is, the invariant on its result should be > 0 <= days < 30, not merely days < 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? ---(end of broadcast)--- TIP 6: explain analyze is your friend
