Tom Lane wrote:
> David Jarvis 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
* htt
David Jarvis 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To m
> Fair enough. How about something like make_timestamp? It's at least
> shorter and easier than construct :-)
>
Agreed.
Dave
On Mon, Jun 14, 2010 at 15:59, Tom Lane wrote:
> Magnus Hagander writes:
>> On Sun, Jun 13, 2010 at 21:19, David Jarvis 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 confusio
Magnus Hagander writes:
> On Sun, Jun 13, 2010 at 21:19, David Jarvis 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 th
>
> 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 plus
. (Not to mention explaining it.) ;-)
SELECT to_timestamp( minutes := 19 ) -- er
On Sun, Jun 13, 2010 at 21:19, David Jarvis 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 usin
On Sun, Jun 13, 2010 at 17:58, Tom Lane wrote:
> Magnus Hagander writes:
>> On Sun, Jun 13, 2010 at 17:42, Tom Lane 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
>>
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
Magnus Hagander writes:
> On Sun, Jun 13, 2010 at 17:42, Tom Lane 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 tha
On Sun, Jun 13, 2010 at 17:42, Tom Lane wrote:
> Magnus Hagander writes:
>> On Sun, Jun 13, 2010 at 09:38, David Jarvis 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
Magnus Hagander writes:
> On Sun, Jun 13, 2010 at 09:38, David Jarvis 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
Heikki Linnakangas 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 small
On Sun, Jun 13, 2010 at 09:38, David Jarvis 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. O
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
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; t
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 y
Hi, Tom.
extract(YEAR FROM m.taken)
I thought that returned a double precision?
Dave
David Jarvis 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 co
David Jarvis 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
>
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);
Datu
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 t
David Jarvis 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_
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);
}
Compil
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,
David Jarvis 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
> Variatio
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);
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.depes
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
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 se
30 matches
Mail list logo
