pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Analysis Function
30+ messages / 7 participants
[nested] [flat]
* Re: Analysis Function
@ 2010-06-11 00:41 David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 00:41 UTC (permalink / raw)
To: pgsql-performance
Hi,
I found a slow part of the query:
SELECT
* date(extract(YEAR FROM m.taken)||'-1-1') d1,*
* date(extract(YEAR FROM m.taken)||'-1-31') d2*
FROM
climate.city c,
climate.station s,
climate.station_category sc,
climate.measurement m
WHERE
c.id = 5148 AND ...
Date extraction is 3.2 seconds, but without is 1.5 seconds. The PL/pgSQL
code that actually runs (where p_month1, p_day1, and p_month2, p_day2 are
integers):
* date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
d1,
date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''')
d2
*
What is a better way to create those dates (without string concatenation, I
presume)?
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 02:11 Andy Colson <andy@squeakycode.net>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Andy Colson @ 2010-06-11 02:11 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; pgsql-performance
On 06/10/2010 07:41 PM, David Jarvis wrote:
> Hi,
>
> I found a slow part of the query:
>
> SELECT
> * date(extract(YEAR FROM m.taken)||'-1-1') d1,*
> * date(extract(YEAR FROM m.taken)||'-1-31') d2*
> FROM
> climate.city c,
> climate.station s,
> climate.station_category sc,
> climate.measurement m
> WHERE
> c.id <http://c.id; = 5148 AND ...
>
> Date extraction is 3.2 seconds, but without is 1.5 seconds. The PL/pgSQL
> code that actually runs (where p_month1, p_day1, and p_month2, p_day2
> are integers):
>
> * date(extract(YEAR FROM
> m.taken)||''-'||p_month1||'-'||p_day1||''') d1,
> date(extract(YEAR FROM
> m.taken)||''-'||p_month2||'-'||p_day2||''') d2
> *
> What is a better way to create those dates (without string
> concatenation, I presume)?
>
> Dave
>
I assume you are doing this in a loop? Many Many Many times? cuz:
andy=# select date(extract(year from current_date) || '-1-1');
date
------------
2010-01-01
(1 row)
Time: 0.528 ms
Its pretty quick. You say "without" its 1.5 seconds? Thats all you change? Can we see the sql and 'explain analyze' for both?
-Andy
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 02:56 David Jarvis <thangalin@gmail.com>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 2 replies; 30+ messages in thread
From: David Jarvis @ 2010-06-11 02:56 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
Hi, Andy.
I assume you are doing this in a loop? Many Many Many times? cuz:
>
Yes. Here are the variations I have benchmarked (times are best of three):
Variation #0
-no date field-
Explain: http://explain.depesz.com/s/Y9R
Time: 2.2s
Variation #1
date('1960-1-1')
Explain: http://explain.depesz.com/s/DW2
Time: 2.6s
Variation #2
date('1960'||'-1-1')
Explain: http://explain.depesz.com/s/YuX
Time: 3.1s
Variation #3
date(extract(YEAR FROM m.taken)||'-1-1')
Explain: http://explain.depesz.com/s/1I
Time: 4.3s
Variation #4
to_date( date_part('YEAR', m.taken)::text, 'YYYY' ) + interval '0 months' +
interval '0 days'
Explain: http://explain.depesz.com/s/fIT
Time: 4.4s
What I would like is along Variation #5:
*PGTYPESdate_mdyjul(taken_year, p_month1, p_day1)*
Time: 2.3s
I find it interesting that variation #2 is half a second slower than
variation #1.
The other question I have is: why does PG seem to discard the results? In
pgAdmin3, I can keep pressing F5 and (before 8.4.4?) the results came back
in 4s for the first response then 1s in subsequent responses.
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 08:25 David Jarvis <thangalin@gmail.com>
parent: David Jarvis <thangalin@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 08:25 UTC (permalink / raw)
To: pgsql-performance
Hi,
To avoid string concatenation using dates, I figured I could write a C
function:
#include "postgres.h"
#include "fmgr.h"
#include "utils/date.h"
#include "utils/nabstime.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum dateserial (PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1 (dateserial);
Datum dateserial (PG_FUNCTION_ARGS) {
int32 p_year = PG_GETARG_INT32(0);
int32 p_month = PG_GETARG_INT32(1);
int32 p_day = PG_GETARG_INT32(2);
DateADT d = date2j (p_year, p_month, p_day) - POSTGRES_EPOCH_JDATE;
PG_RETURN_DATEADT(d);
}
Compiles without errors or warnings. The function is integrated as follows:
CREATE OR REPLACE FUNCTION dateserial(integer, integer, integer)
RETURNS text AS
'ymd.so', 'dateserial'
LANGUAGE 'c' IMMUTABLE STRICT
COST 1;
However, when I try to use it, the database segfaults:
select dateserial( 2007, 1, 3 )
Any ideas why?
Thank you!
Dave
P.S.
I have successfully written a function that creates a YYYYmmDD formatted
string (using *sprintf*) when given three integers. It returns as expected;
I ran it as follows:
dateserial( extract(YEAR FROM m.taken)::int, 1, 1 )::date
This had a best-of-three time of 3.7s compared with 4.3s using string
concatenation. If I can eliminate all the typecasts, and pass in m.taken
directly (rather than calling *extract*), I think the speed will be closer
to 2.5s.
Any hints would be greatly appreciated.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 08:57 Tim Landscheidt <tim@tim-landscheidt.de>
parent: David Jarvis <thangalin@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Tim Landscheidt @ 2010-06-11 08:57 UTC (permalink / raw)
To: pgsql-performance
David Jarvis <thangalin@gmail.com> wrote:
> [...]
> Yes. Here are the variations I have benchmarked (times are best of three):
> Variation #0
> -no date field-
> Explain: http://explain.depesz.com/s/Y9R
> Time: 2.2s
> Variation #1
> date('1960-1-1')
> Explain: http://explain.depesz.com/s/DW2
> Time: 2.6s
> Variation #2
> date('1960'||'-1-1')
> Explain: http://explain.depesz.com/s/YuX
> Time: 3.1s
> Variation #3
> date(extract(YEAR FROM m.taken)||'-1-1')
> Explain: http://explain.depesz.com/s/1I
> Time: 4.3s
> Variation #4
> to_date( date_part('YEAR', m.taken)::text, 'YYYY' ) + interval '0 months' +
> interval '0 days'
> Explain: http://explain.depesz.com/s/fIT
> Time: 4.4s
> What I would like is along Variation #5:
> *PGTYPESdate_mdyjul(taken_year, p_month1, p_day1)*
> Time: 2.3s
> I find it interesting that variation #2 is half a second slower than
> variation #1.
> [...]
Have you tested DATE_TRUNC()?
Tim
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 09:18 David Jarvis <thangalin@gmail.com>
parent: Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 09:18 UTC (permalink / raw)
To: pgsql-performance
Hi, Tim.
Have you tested DATE_TRUNC()?
>
Not really; it returns a full timestamp and I would still have to
concatenate strings. My goal is to speed up the following code (where
*p_*parameters are user inputs):
* date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
d1,
date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''')
d2*
Using DATE_TRUNC() won't help here, as far as I can tell. Removing the
concatenation will halve the query's time. Such as:
dateserial( m.taken, p_month1, p_day1 ) d1,
dateserial( m.taken, p_month2, p_day2 ) d2
My testing so far has shown a modest improvement by using a C function (to
avoid concatenation).
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 09:42 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Heikki Linnakangas @ 2010-06-11 09:42 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: pgsql-performance
On 11/06/10 11:25, David Jarvis wrote:
> Datum dateserial (PG_FUNCTION_ARGS) {
> int32 p_year = PG_GETARG_INT32(0);
> int32 p_month = PG_GETARG_INT32(1);
> int32 p_day = PG_GETARG_INT32(2);
>
> DateADT d = date2j (p_year, p_month, p_day) - POSTGRES_EPOCH_JDATE;
> PG_RETURN_DATEADT(d);
> }
>
> Compiles without errors or warnings. The function is integrated as follows:
>
> CREATE OR REPLACE FUNCTION dateserial(integer, integer, integer)
> RETURNS text AS
> 'ymd.so', 'dateserial'
> LANGUAGE 'c' IMMUTABLE STRICT
> COST 1;
>
> However, when I try to use it, the database segfaults:
>
> select dateserial( 2007, 1, 3 )
>
> Any ideas why?
The C function returns a DateADT, which is a typedef for int32, but the
CREATE FUNCTION statement claims that it returns 'text'.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 11:18 Tim Landscheidt <tim@tim-landscheidt.de>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Tim Landscheidt @ 2010-06-11 11:18 UTC (permalink / raw)
To: pgsql-performance
David Jarvis <thangalin@gmail.com> wrote:
>> Have you tested DATE_TRUNC()?
> Not really; it returns a full timestamp and I would still have to
> concatenate strings. My goal is to speed up the following code (where
> *p_*parameters are user inputs):
> * date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
> d1,
> date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''')
> d2*
> Using DATE_TRUNC() won't help here, as far as I can tell. Removing the
> concatenation will halve the query's time. Such as:
> dateserial( m.taken, p_month1, p_day1 ) d1,
> dateserial( m.taken, p_month2, p_day2 ) d2
> My testing so far has shown a modest improvement by using a C function (to
> avoid concatenation).
You could use:
| (DATE_TRUNC('year', m.taken) + p_month1 * '1 month'::INTERVAL + p_day1 * '1 day'::INTERVAL)::DATE
but whether that is faster or slower I don't know. But I
don't see why this query needs to be fast in the first
place. It seems to be interactive, and therefore I wouldn't
invest too much time to have the user wait not 4.4, but
2.2 seconds. You could also do the concatenation in the ap-
plication if that is faster than PostgreSQL's date arithme-
tics.
Tim
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 17:09 David Jarvis <thangalin@gmail.com>
parent: Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 2 replies; 30+ messages in thread
From: David Jarvis @ 2010-06-11 17:09 UTC (permalink / raw)
To: pgsql-performance
Hi,
The C function returns a DateADT, which is a typedef for int32, but the
> CREATE FUNCTION statement claims that it returns 'text'.
>
That'll do it. Thank you!
but whether that is faster or slower I don't know. But I
> don't see why this query needs to be fast in the first
> place. It seems to be interactive, and therefore I wouldn't
>
When users click the button, I want the result returned in in less under 4
seconds. Right now it is closer to 10. Consequently, they click twice.
Shaving 2 seconds here and there will make a huge difference. It will also
allow the computer to handle a higher volume of requests.
> invest too much time to have the user wait not 4.4, but
> 2.2 seconds. You could also do the concatenation in the ap-
> plication if that is faster than PostgreSQL's date arithme-
> tics.
>
No, I cannot. The concatenation uses the year that the measurement was
made--from the database--and the month/day combination from the user. See
also:
http://stackoverflow.com/questions/2947105/calculate-year-for-end-date-postgresql
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 18:17 David Jarvis <thangalin@gmail.com>
parent: David Jarvis <thangalin@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 18:17 UTC (permalink / raw)
To: pgsql-performance
Hi,
Here is code to convert dates from integers without string concatenation:
Edit dateserial.c:
#include "postgres.h"
#include "utils/date.h"
#include "utils/nabstime.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum dateserial(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1 (dateserial);
Datum
dateserial(PG_FUNCTION_ARGS) {
int32 p_year = (int32)PG_GETARG_FLOAT8(0);
int32 p_month = PG_GETARG_INT32(1);
int32 p_day = PG_GETARG_INT32(2);
PG_RETURN_DATEADT( date2j( p_year, p_month, p_day ) - POSTGRES_EPOCH_JDATE );
}
Edit Makefile:
MODULES = dateserial
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
Edit inst.sh (optional):
#!/bin/bash
make clean && make && strip *.so && make install &&
/etc/init.d/postgresql-8.4 restart
Run bash inst.sh.
Create a SQL function dateserial:
CREATE OR REPLACE FUNCTION dateserial(double precision, integer, integer)
RETURNS date AS
'$libdir/dateserial', 'dateserial'
LANGUAGE 'c' IMMUTABLE STRICT
COST 1;
ALTER FUNCTION dateserial(double precision, integer, integer) OWNER TO postgres;
Test the function:
SELECT dateserial( 2007, 5, 5 )
Using this function, performance increases from 4.4s to 2.8s..
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 18:53 Tim Landscheidt <tim@tim-landscheidt.de>
parent: David Jarvis <thangalin@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Tim Landscheidt @ 2010-06-11 18:53 UTC (permalink / raw)
To: pgsql-performance
David Jarvis <thangalin@gmail.com> wrote:
> [...]
>> invest too much time to have the user wait not 4.4, but
>> 2.2 seconds. You could also do the concatenation in the ap-
>> plication if that is faster than PostgreSQL's date arithme-
>> tics.
> No, I cannot. The concatenation uses the year that the measurement was
> made--from the database--and the month/day combination from the user. See
> also:
> http://stackoverflow.com/questions/2947105/calculate-year-for-end-date-postgresql
That page doesn't deal with "select year from database and
month/day from user and present the results", but *much*
different problems.
Tim
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 20:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-11 20:20 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: pgsql-performance
David Jarvis <thangalin@gmail.com> writes:
> dateserial(PG_FUNCTION_ARGS) {
> int32 p_year = (int32)PG_GETARG_FLOAT8(0);
> int32 p_month = PG_GETARG_INT32(1);
> int32 p_day = PG_GETARG_INT32(2);
Er ... why float? Integer is plenty for the range of years supported by
the PG datetime infrastructure. The above coding is pretty lousy in terms
of its roundoff and overflow behavior, too.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 20:30 David Jarvis <thangalin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 20:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi, Tom.
extract(YEAR FROM m.taken)
I thought that returned a double precision?
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-11 20:38 David Jarvis <thangalin@gmail.com>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-11 20:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi,
I added an explicit cast in the SQL:
dateserial(extract(YEAR FROM
m.taken)::int,'||p_month1||','||p_day1||') d1,
dateserial(extract(YEAR FROM
m.taken)::int,'||p_month2||','||p_day2||') d2
The function now takes three integer parameters; there was no performance
loss.
Thank you.
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 06:02 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Heikki Linnakangas @ 2010-06-13 06:02 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@postgresql.org, Magnus Hagander <magnus@hagander.net>
On 11/06/10 23:38, David Jarvis wrote:
> I added an explicit cast in the SQL:
>
> dateserial(extract(YEAR FROM
> m.taken)::int,'||p_month1||','||p_day1||') d1,
> dateserial(extract(YEAR FROM
> m.taken)::int,'||p_month2||','||p_day2||') d2
>
> The function now takes three integer parameters; there was no performance
> loss.
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.
We could have a function like:
construct_timestamp(year int4, month int4, date int4, hour int4, minute
int4, second int4, milliseconds int4, timezone text)
Now that we have named parameter notation, callers can use it to
conveniently fill in only the fields needed:
SELECT construct_timestamp(year := 1999, month := 10, date := 22);
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 07:38 David Jarvis <thangalin@gmail.com>
parent: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
1 sibling, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-13 07:38 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@postgresql.org, Magnus Hagander <magnus@hagander.net>
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.
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.
Does it makes sense to use named parameter notation for the first value (the
year)? This could be potentially confusing:
to_date() - What would this return? now()? Jan 1st, 1970? 2000?
Similarly, to_timestamp() ...? Seems meaningless without at least a full
date and an hour.
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 09:42 Magnus Hagander <magnus@hagander.net>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Magnus Hagander @ 2010-06-13 09:42 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Sun, Jun 13, 2010 at 09:38, David Jarvis <thangalin@gmail.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/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 15:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
1 sibling, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-13 15:34 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: David Jarvis <thangalin@gmail.com>; pgsql-performance@postgresql.org, Magnus Hagander <magnus@hagander.net>
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> We could have a function like:
> construct_timestamp(year int4, month int4, date int4, hour int4, minute
> int4, second int4, milliseconds int4, timezone text)
This fails to allow specification to the microsecond level (and note
that with float timestamps even smaller fractions have potential use).
I would suggest dropping the milliseconds argument and instead letting
the seconds arg be float8. That seems a closer match to the way people
think about the textual representation.
> Now that we have named parameter notation, callers can use it to
> conveniently fill in only the fields needed:
It's not immediately obvious what the default value of "timezone"
will be?
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 15:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-13 15:42 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
Magnus Hagander <magnus@hagander.net> writes:
> On Sun, Jun 13, 2010 at 09:38, David Jarvis <thangalin@gmail.com> wrote:
>> 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?
There's no reason not to allow the year parameter to be named. What
I think it shouldn't have is a default. OTOH I see no good reason
not to allow the other ones to have defaults. (We presumably want
timezone to default to the system timezone setting, but I wonder how
we should make that work --- should an empty string be treated as
meaning that?)
>> Similarly, to_timestamp() ...? Seems meaningless without at least a full
>> date and an hour.
> Agreed.
No, I think it's perfectly sane to allow month/day to default to 1
and h/m/s to zeroes.
I do think it might be a good idea to have two functions,
construct_timestamp yielding timestamptz and construct_date
yielding date (and needing only 3 args). When you only want
a date, having to use construct_timestamp and cast will be
awkward and much more expensive than is needed (timezone
rotations aren't real cheap).
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 15:49 Magnus Hagander <magnus@hagander.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: Magnus Hagander @ 2010-06-13 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
On Sun, Jun 13, 2010 at 17:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Sun, Jun 13, 2010 at 09:38, David Jarvis <thangalin@gmail.com> wrote:
>>> 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?
>
> There's no reason not to allow the year parameter to be named. What
> I think it shouldn't have is a default. OTOH I see no good reason
> not to allow the other ones to have defaults. (We presumably want
> timezone to default to the system timezone setting, but I wonder how
> we should make that work --- should an empty string be treated as
> meaning that?)
Umm. NULL could be made to mean that, or we could provicde two
different versions - one that takes TZ and one that doesn't.
>>> Similarly, to_timestamp() ...? Seems meaningless without at least a full
>>> date and an hour.
>
>> Agreed.
>
> No, I think it's perfectly sane to allow month/day to default to 1
> and h/m/s to zeroes.
>
> I do think it might be a good idea to have two functions,
> construct_timestamp yielding timestamptz and construct_date
> yielding date (and needing only 3 args). When you only want
> a date, having to use construct_timestamp and cast will be
> awkward and much more expensive than is needed (timezone
> rotations aren't real cheap).
And a third, construct_time(), no?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 15:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-13 15:58 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
Magnus Hagander <magnus@hagander.net> writes:
> On Sun, Jun 13, 2010 at 17:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... (We presumably want
>> timezone to default to the system timezone setting, but I wonder how
>> we should make that work --- should an empty string be treated as
>> meaning that?)
> Umm. NULL could be made to mean that, or we could provicde two
> different versions - one that takes TZ and one that doesn't.
Using NULL like that seems a bit awkward: for one thing it'd mean the
function couldn't be STRICT, and also it'd be bizarre that only this
one argument could be null without leading to a null result.
And two separate functions isn't good either. Basically, I think it's
important that there be a way to specify an explicit parameter value
that behaves identically to the default.
> And a third, construct_time(), no?
Yeah, maybe ... do you think there's any demand for it?
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-13 19:19 David Jarvis <thangalin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: David Jarvis @ 2010-06-13 19:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance@postgresql.org, Magnus Hagander <magnus@hagander.net>
Hi,
It's not immediately obvious what the default value of "timezone"
> will be?
>
The system's locale, like now(); documentation can clarify.
By named parameter, I meant default value. You could construct a timestamp
variable using:
construct_timestamp( year := 1900, hour := 1 )
When I read that code, the first thing I think it should return is:
1900-01-01 01:00:00.0000-07
I agree construct_timestamp( hour := 1 ) and construct_date() are errors:
year is required.
Dave
P.S.
I prefer to_timestamp and to_date over the more verbose construct_timestamp.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-14 09:01 Magnus Hagander <magnus@hagander.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 30+ messages in thread
From: Magnus Hagander @ 2010-06-14 09:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
On Sun, Jun 13, 2010 at 17:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Sun, Jun 13, 2010 at 17:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> ... (We presumably want
>>> timezone to default to the system timezone setting, but I wonder how
>>> we should make that work --- should an empty string be treated as
>>> meaning that?)
>
>> Umm. NULL could be made to mean that, or we could provicde two
>> different versions - one that takes TZ and one that doesn't.
>
> Using NULL like that seems a bit awkward: for one thing it'd mean the
> function couldn't be STRICT, and also it'd be bizarre that only this
> one argument could be null without leading to a null result.
Hmm, yeah.
> And two separate functions isn't good either. Basically, I think it's
> important that there be a way to specify an explicit parameter value
> that behaves identically to the default.
In that case, empty string seems fairly reasonable - if you look at
the text based parsing, that's what we do if the timezone is an "empty
string" (meaning not specified).
>> And a third, construct_time(), no?
>
> Yeah, maybe ... do you think there's any demand for it?
Yes, I think there is. Plus, it's for completeness :-)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-14 09:03 Magnus Hagander <magnus@hagander.net>
parent: David Jarvis <thangalin@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Magnus Hagander @ 2010-06-14 09:03 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
On Sun, Jun 13, 2010 at 21:19, David Jarvis <thangalin@gmail.com> wrote:
> Hi,
>
>> It's not immediately obvious what the default value of "timezone"
>> will be?
>
> The system's locale, like now(); documentation can clarify.
>
> By named parameter, I meant default value. You could construct a timestamp
> variable using:
>
> construct_timestamp( year := 1900, hour := 1 )
>
> When I read that code, the first thing I think it should return is:
>
> 1900-01-01 01:00:00.0000-07
>
> I agree construct_timestamp( hour := 1 ) and construct_date() are errors:
> year is required.
Does it make sense to allow minutes when hours isn't specified? Or
should we simply say that for each of the date and the time part, to
specify at <level n> you need to have everything from the top up to
<level n-1> specified? E.g. month requires year to be specified, day
requires both year and month etc?
> I prefer to_timestamp and to_date over the more verbose construct_timestamp.
Yeah, I agree with that.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-14 11:10 David Jarvis <thangalin@gmail.com>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 0 replies; 30+ messages in thread
From: David Jarvis @ 2010-06-14 11:10 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
>
> Does it make sense to allow minutes when hours isn't specified? Or
>
For time, 00 seems a reasonable default for all values; clearly document the
defaults. Also, having a default makes the code simpler than <level n> plus
<level n-1>. (Not to mention explaining it.) ;-)
SELECT to_timestamp( minutes := 19 ) -- error (year not specified)
SELECT to_timestamp( year := 2000, minutes := 19 ) -- 2000-01-01
00:19:00.0000-07
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-14 13:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-14 13:59 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
Magnus Hagander <magnus@hagander.net> writes:
> On Sun, Jun 13, 2010 at 21:19, David Jarvis <thangalin@gmail.com> wrote:
>> I prefer to_timestamp and to_date over the more verbose construct_timestamp.
> Yeah, I agree with that.
Those names are already taken. It will cause confusion (of both people
and machines) if you try to overload them with this.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-16 06:36 Magnus Hagander <magnus@hagander.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 30+ messages in thread
From: Magnus Hagander @ 2010-06-16 06:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Jarvis <thangalin@gmail.com>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
On Mon, Jun 14, 2010 at 15:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Sun, Jun 13, 2010 at 21:19, David Jarvis <thangalin@gmail.com> wrote:
>>> I prefer to_timestamp and to_date over the more verbose construct_timestamp.
>
>> Yeah, I agree with that.
>
> Those names are already taken. It will cause confusion (of both people
> and machines) if you try to overload them with this.
Fair enough. How about something like make_timestamp? It's at least
shorter and easier than construct :-)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-16 08:48 David Jarvis <thangalin@gmail.com>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 0 replies; 30+ messages in thread
From: David Jarvis @ 2010-06-16 08:48 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
> Fair enough. How about something like make_timestamp? It's at least
> shorter and easier than construct :-)
>
Agreed.
Dave
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-06-16 14:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 30+ messages in thread
From: Tom Lane @ 2010-06-16 14:33 UTC (permalink / raw)
To: David Jarvis <thangalin@gmail.com>; +Cc: Magnus Hagander <magnus@hagander.net>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
David Jarvis <thangalin@gmail.com> writes:
>> Fair enough. How about something like make_timestamp? It's at least
>> shorter and easier than construct :-)
> Agreed.
No objection here either.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Analysis Function
@ 2010-07-01 00:42 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 30+ messages in thread
From: Bruce Momjian @ 2010-07-01 00:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Jarvis <thangalin@gmail.com>; Magnus Hagander <magnus@hagander.net>; Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance
Tom Lane wrote:
> David Jarvis <thangalin@gmail.com> writes:
> >> Fair enough. How about something like make_timestamp? It's at least
> >> shorter and easier than construct :-)
>
> > Agreed.
>
> No objection here either.
Added to TODO:
Add function to allow the creation of timestamps using parameters
* http://archives.postgresql.org/pgsql-performance/2010-06/msg00232.php
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2010-07-01 00:42 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-11 00:41 Re: Analysis Function David Jarvis <thangalin@gmail.com>
2010-06-11 02:11 ` Andy Colson <andy@squeakycode.net>
2010-06-11 02:56 ` David Jarvis <thangalin@gmail.com>
2010-06-11 08:25 ` David Jarvis <thangalin@gmail.com>
2010-06-11 09:42 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-11 08:57 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-06-11 09:18 ` David Jarvis <thangalin@gmail.com>
2010-06-11 11:18 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-06-11 17:09 ` David Jarvis <thangalin@gmail.com>
2010-06-11 18:17 ` David Jarvis <thangalin@gmail.com>
2010-06-11 20:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-11 20:30 ` David Jarvis <thangalin@gmail.com>
2010-06-11 20:38 ` David Jarvis <thangalin@gmail.com>
2010-06-13 06:02 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-13 07:38 ` David Jarvis <thangalin@gmail.com>
2010-06-13 09:42 ` Magnus Hagander <magnus@hagander.net>
2010-06-13 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-13 15:49 ` Magnus Hagander <magnus@hagander.net>
2010-06-13 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-14 09:01 ` Magnus Hagander <magnus@hagander.net>
2010-06-13 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-13 19:19 ` David Jarvis <thangalin@gmail.com>
2010-06-14 09:03 ` Magnus Hagander <magnus@hagander.net>
2010-06-14 11:10 ` David Jarvis <thangalin@gmail.com>
2010-06-14 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-16 06:36 ` Magnus Hagander <magnus@hagander.net>
2010-06-16 08:48 ` David Jarvis <thangalin@gmail.com>
2010-06-16 14:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-01 00:42 ` Bruce Momjian <bruce@momjian.us>
2010-06-11 18:53 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox