Tom Lane wrote:
David Jarvis thanga...@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
On Mon, Jun 14, 2010 at 15:59, Tom Lane t...@sss.pgh.pa.us wrote:
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 21:19, David Jarvis thanga...@gmail.com wrote:
I prefer to_timestamp and to_date over the more verbose construct_timestamp.
Yeah, I agree with that.
Those
Fair enough. How about something like make_timestamp? It's at least
shorter and easier than construct :-)
Agreed.
Dave
David Jarvis thanga...@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
--
Sent via pgsql-performance mailing list
On Sun, Jun 13, 2010 at 17:58, Tom Lane t...@sss.pgh.pa.us wrote:
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 17:42, Tom Lane t...@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
On Sun, Jun 13, 2010 at 21:19, David Jarvis thanga...@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
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
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 21:19, David Jarvis thanga...@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
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;
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 Sun, Jun 13, 2010 at 09:38, David Jarvis thanga...@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
Heikki Linnakangas heikki.linnakan...@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
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 09:38, David Jarvis thanga...@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?
On Sun, Jun 13, 2010 at 17:42, Tom Lane t...@sss.pgh.pa.us wrote:
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 09:38, David Jarvis thanga...@gmail.com wrote:
Does it makes sense to use named parameter notation for the first value (the
year)? This could be potentially
Magnus Hagander mag...@hagander.net writes:
On Sun, Jun 13, 2010 at 17:42, Tom Lane t...@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.
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
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
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);
}
David Jarvis thanga...@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
David Jarvis thanga...@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
Hi, Tom.
extract(YEAR FROM m.taken)
I thought that returned a double precision?
Dave
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
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
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
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:
25 matches
Mail list logo
