Thread: Integer input functions for date and timestamp
Hi folks, In my own databases, I've been using a couple of C functions that might be useful to the wider community. They are very simple date/timestamp constructors that take integers as their arguments. Nothing fancy, but very convenient and *much* faster than using a SQL or PL/pgSQL workaround. The offering is analogous to mktime() in C/PHP, the standard datetime constructors in Python, and Perl's Time::Local. The function signatures pretty much speak for themselves: date(year int, month int, day int) returns date datetime(year int, month int, day int, hour int, minute int, second int) returns timestamp Without these functions (or some variation), a user wishing to construct a date from integers can only assemble the date into a string and then put that string through postgres' datetime parser, which is totally perverse. Is there any interest in adding this to core, or failing that, contrib? If so I'd be happy to provide a patch including the functions themselves and some attendant documentation. I'm not wedded to the function names or argument order, and I realise a fully realised offering would need to include a variant for 'timestamp with time zone'. Cheers, BJ
On 22 October 2010 19:45, Brendan Jurd <direvus@gmail.com> wrote: > Hi folks, > > In my own databases, I've been using a couple of C functions that > might be useful to the wider community. > > They are very simple date/timestamp constructors that take integers as > their arguments. Nothing fancy, but very convenient and *much* faster > than using a SQL or PL/pgSQL workaround. > > The offering is analogous to mktime() in C/PHP, the standard datetime > constructors in Python, and Perl's Time::Local. The function > signatures pretty much speak for themselves: > > date(year int, month int, day int) returns date > datetime(year int, month int, day int, hour int, minute int, second > int) returns timestamp > > Without these functions (or some variation), a user wishing to > construct a date from integers can only assemble the date into a > string and then put that string through postgres' datetime parser, > which is totally perverse. > > Is there any interest in adding this to core, or failing that, > contrib? If so I'd be happy to provide a patch including the > functions themselves and some attendant documentation. > > I'm not wedded to the function names or argument order, and I realise > a fully realised offering would need to include a variant for > 'timestamp with time zone'. What's wrong with to_timestamp() and to_date()? Sure, your functions might be marginally faster, but I don't think that it's likely to be a very performance sensitive area. -- Regards, Peter Geoghegan
On Fri, Oct 22, 2010 at 2:45 PM, Brendan Jurd <direvus@gmail.com> wrote: > date(year int, month int, day int) returns date > datetime(year int, month int, day int, hour int, minute int, second > int) returns timestamp > > Without these functions (or some variation), a user wishing to > construct a date from integers can only assemble the date into a > string and then put that string through postgres' datetime parser, > which is totally perverse. > > Is there any interest in adding this to core, or failing that, > contrib? If so I'd be happy to provide a patch including the > functions themselves and some attendant documentation. I think that would be useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 23 October 2010 05:58, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote: > On 22 October 2010 19:45, Brendan Jurd <direvus@gmail.com> wrote: >> Without these functions (or some variation), a user wishing to >> construct a date from integers can only assemble the date into a >> string and then put that string through postgres' datetime parser, >> which is totally perverse. > > What's wrong with to_timestamp() and to_date()? Sure, your functions > might be marginally faster, but I don't think that it's likely to be a > very performance sensitive area. > Hi Peter, The answer to your question is in the paragraph I quoted from my OP above. to_timestamp() and to_date() don't offer any non-retarded way to get from integer values to datetime values. They are great if you are coming from text, but if you already have integers they are lame. Perhaps an example would be constructive. Would you rather do this: datetime(2010, 10, 23, 6, 11, 0) or this: to_date(2010::text || '-' || 10::text || '-' || 23::text || ' ' || 6::text || ':' || 11::text || ':' || 0::text, 'YYYY-MM-DD HH24:MI:SS') The performance increase is nice, but as you say, this isn't likely to be in a performance critical path. The main benefits are convenience, simplicity and readability. Cheers, BJ
Brendan,
> date(year int, month int, day int) returns date
> datetime(year int, month int, day int, hour int, minute int, second
> int) returns timestamp
a) you'd need to rename these.
b) we'd also want the inverse of these, which would be extremely useful.
> Without these functions (or some variation), a user wishing to
> construct a date from integers can only assemble the date into a
> string and then put that string through postgres' datetime parser,
> which is totally perverse.
"Parsers Gone Wild!" ;-)
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On 23 October 2010 06:15, Josh Berkus <josh@agliodbs.com> wrote: > a) you'd need to rename these. I'm open to that. What names would you propose? > b) we'd also want the inverse of these, which would be extremely useful. Not a problem. Cheers, BJ
On Fri, Oct 22, 2010 at 3:20 PM, Brendan Jurd <direvus@gmail.com> wrote: > On 23 October 2010 06:15, Josh Berkus <josh@agliodbs.com> wrote: >> a) you'd need to rename these. > > I'm open to that. What names would you propose? > >> b) we'd also want the inverse of these, which would be extremely useful. > > Not a problem. Ooh, I like it. A related personal pet peeve of mine: AFAIK the easiest way to convert from an integer number of seconds to an interval representing that many seconds is: (the_int || ' s')::interval I guess we don't want to get too carried away with this, but has anyone else gotten annoyed by this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of vie oct 22 16:54:01 -0300 2010: > Ooh, I like it. A related personal pet peeve of mine: AFAIK the > easiest way to convert from an integer number of seconds to an > interval representing that many seconds is: > > (the_int || ' s')::interval > > I guess we don't want to get too carried away with this, but has > anyone else gotten annoyed by this? Why do you go through text and concatenation? I advocate this approach:the_int * interval '1 second' I haven't measured it though. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Excerpts from Alvaro Herrera's message of vie oct 22 17:13:31 -0300 2010: > Excerpts from Robert Haas's message of vie oct 22 16:54:01 -0300 2010: > > > Ooh, I like it. A related personal pet peeve of mine: AFAIK the > > easiest way to convert from an integer number of seconds to an > > interval representing that many seconds is: > > > > (the_int || ' s')::interval > > Why do you go through text and concatenation? I advocate this approach: > the_int * interval '1 second' A quick tests with pgbench custom scripts says that it's about 10% faster. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Oct 22, 2010 at 4:27 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Alvaro Herrera's message of vie oct 22 17:13:31 -0300 2010: >> Excerpts from Robert Haas's message of vie oct 22 16:54:01 -0300 2010: >> >> > Ooh, I like it. A related personal pet peeve of mine: AFAIK the >> > easiest way to convert from an integer number of seconds to an >> > interval representing that many seconds is: >> > >> > (the_int || ' s')::interval >> >> Why do you go through text and concatenation? I advocate this approach: >> the_int * interval '1 second' > > A quick tests with pgbench custom scripts says that it's about 10% > faster. Hmm, never thought of that approach. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> Ooh, I like it. A related personal pet peeve of mine: AFAIK the
> easiest way to convert from an integer number of seconds to an
> interval representing that many seconds is:
> (the_int || ' s')::interval
No, the standard solution is
the_int * '1 second'::interval
which is fast and adapts readily to different units for the interval
number.
regards, tom lane