Re: 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(at)gmail(dot)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(at)gmail(dot)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(dot)geoghegan86(at)gmail(dot)com> wrote:
> On 22 October 2010 19:45, Brendan Jurd <direvus(at)gmail(dot)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(at)agliodbs(dot)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(at)gmail(dot)com> wrote:
> On 23 October 2010 06:15, Josh Berkus <josh(at)agliodbs(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)gmail(dot)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