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

Re: Analysis Function

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: David Jarvis <thangalin(at)gmail(dot)com>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Analysis Function
Date: 2010-06-13 09:42:50
Message-ID: AANLkTil3RK4qbICWfbyJNJi7lxyNnFHwWlhaLmsZh3Rn@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Jun 13, 2010 at 09:38, David Jarvis <thangalin(at)gmail(dot)com> wrote:
> Hi,
>
>> We had a little chat about this with Magnus. It's pretty surprising that
>> there's no built-in function to do this, we should consider adding one.
>
> I agree; you should be able to create a timestamp or a date from integer
> values. Others, apparently, have written code. The implementation I did was
> pretty rudimentary, but I was going for speed.
>
> If you could overload to_date and to_timestamp, that would be great. For
> example:
>
> to_date( year ) = year-01-01
> to_date( year, month ) = year-month-01
> to_date( year, month, day ) = year-month-day
>
> to_timestamp( year, month, day, hour ) = year-month-day hour:00:00.0000 GMT
> etc.

Not that it would make a huge difference over having to specify 1's
and 0's there, but I agree that could be useful.


>> construct_timestamp(year int4, month int4, date int4, hour int4, minute
>> int4, second int4, milliseconds int4, timezone text)
>
> Also, "date int4" should be "day int4", to avoid confusion with the date
> type.

Yes, absolutely.


> Does it makes sense to use named parameter notation for the first value (the
> year)? This could be potentially confusing:

How so? If it does named parameters, why not all?


> to_date() - What would this return? now()? Jan 1st, 1970? 2000?

ERROR, IMHO. We have a function for now() already, and the others are
so arbitrary there is no way to explain such a choice.


> Similarly, to_timestamp() ...? Seems meaningless without at least a full
> date and an hour.

Agreed.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

In response to

Responses

pgsql-performance by date

Next:From: AI RummanDate: 2010-06-13 10:45:31
Subject: Re: query hangs
Previous:From: David JarvisDate: 2010-06-13 07:38:48
Subject: Re: Analysis Function

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