Re: Inputting relative datetimes

As background, I have an app that accepts user text input and casts it
to a timestamp in order to produce reports. I use PostgreSQL's
timestamp input conversion for this, since it gives a lot of
flexibility, and can parse pretty much anything the users throw at it.

It is also handy that it recognizes special case values like "now",
"today", "tomorrow" and "yesterday". However, I can't see any way of
entering more general relative timestamps like "5 days ago" or "2
hours from now".

Obviously I can enhance my app by writing my own input function to
support relative timestamps, but I wonder if this is something that
would be more generally useful if PostgreSQL supported it natively. If
so, what should the syntax be?

My first thought was to have some general way of adding or subtracting
an interval at the end of an input timestamp, eg. by adding another
couple of special values - "plus <interval>" and "minus <interval>".
This would allow things like:

TIMESTAMPTZ 'today minus 5 days'
TIMESTAMPTZ 'now plus 2 hours'

It seems a bit clunky to have to spell out "plus" and "minus", but I
think that using the symbols + and - would be impossible to parse
because of the ambiguity with timezones.

Thoughts?
Better ideas?

Regards,
Dean

On Thu, Aug 25, 2011 at 11:39, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>wrote:

> My first thought was to have some general way of adding or subtracting
> an interval at the end of an input timestamp, eg. by adding another
> couple of special values - "plus <interval>" and "minus <interval>".
> This would allow things like:
>
> TIMESTAMPTZ 'today minus 5 days'
> TIMESTAMPTZ 'now plus 2 hours'
>

Funny you should mention intervals...

timestamptz 'today' - interval '5 days'
timestamptz 'now' + interval '2 hours'

On 25 August 2011 10:43, Vik Reykja <vikreykja(at)gmail(dot)com> wrote:
> On Thu, Aug 25, 2011 at 11:39, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
> wrote:
>>
>> My first thought was to have some general way of adding or subtracting
>> an interval at the end of an input timestamp, eg. by adding another
>> couple of special values - "plus <interval>" and "minus <interval>".
>> This would allow things like:
>>
>> TIMESTAMPTZ 'today minus 5 days'
>> TIMESTAMPTZ 'now plus 2 hours'
>
> Funny you should mention intervals...
>
> timestamptz 'today' - interval '5 days'
> timestamptz 'now' + interval '2 hours'
>

Yes, but what I am trying to achieve is a way of entering such
relative timestamps using a single input value, so that absolute and
relative timestamps can both be bound to a SQL query using just one
variable.

Regards,
Dean

On Thu, Aug 25, 2011 at 5:08 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 25 August 2011 10:43, Vik Reykja <vikreykja(at)gmail(dot)com> wrote:
>> On Thu, Aug 25, 2011 at 11:39, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
>> wrote:
>>>
>>> My first thought was to have some general way of adding or subtracting
>>> an interval at the end of an input timestamp, eg. by adding another
>>> couple of special values - "plus <interval>" and "minus <interval>".
>>> This would allow things like:
>>>
>>> TIMESTAMPTZ 'today minus 5 days'
>>> TIMESTAMPTZ 'now plus 2 hours'
>>
>> Funny you should mention intervals...
>>
>> timestamptz 'today' - interval '5 days'
>> timestamptz 'now' + interval '2 hours'
>>
>
> Yes, but what I am trying to achieve is a way of entering such
> relative timestamps using a single input value, so that absolute and
> relative timestamps can both be bound to a SQL query using just one
> variable.

not gonna happen. as noted, intervals already solve the issue quite
well and timestamp string parsing is already complicated enough as it
is.

merlin

> Yes, but what I am trying to achieve is a way of entering such
> relative timestamps using a single input value, so that absolute and
> relative timestamps can both be bound to a SQL query using just one
> variable.

I think adding a function would be the way to go then. Maybe extending
to_timestamp with a "relative" keyword.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Aug 25, 2011, at 5:08 AM, Dean Rasheed wrote:
>> Funny you should mention intervals...
>>
>> timestamptz 'today' - interval '5 days'
>> timestamptz 'now' + interval '2 hours'
>>
>
> Yes, but what I am trying to achieve is a way of entering such
> relative timestamps using a single input value, so that absolute and
> relative timestamps can both be bound to a SQL query using just one
> variable.

Even if the community doesn't want to add this to core, I think it would be a great add-on to put on PGXN. If you don't feel up to writing it themselves, perhaps you would pay one of the consulting companies to do it for them?
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Fri, Aug 26, 2011 at 4:32 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Aug 25, 2011, at 5:08 AM, Dean Rasheed wrote:
>>> Funny you should mention intervals...
>>>
>>> timestamptz 'today' - interval '5 days'
>>> timestamptz 'now' + interval '2 hours'
>>>
>>
>> Yes, but what I am trying to achieve is a way of entering such
>> relative timestamps using a single input value, so that absolute and
>> relative timestamps can both be bound to a SQL query using just one
>> variable.
>
> Even if the community doesn't want to add this to core, I think it would be a great add-on to put on PGXN. If you don't feel up to writing it themselves, perhaps you would pay one of the consulting companies to do it for them?

Frankly, our current date parsing code is pretty darn strange and
flaky. If nobody's found the energy to rationalize that, what are the
chances that we can add a whole bunch more functionality without also
adding a whole bunch more bugs? For examples of the sorts of things
we haven't gotten around to fixing, see:

http://archives.postgresql.org/pgsql-hackers/2011-03/msg01295.php

Another problem here is that it seems possible, even likely, that
everyone will have their own particular flavor of what they'd like to
see accepted: two weeks ago, a week ago Tuesday, next Saturday, last
Friday, two weeks from Saturday, Christmas plus three fortnights...
if it weren't already spaghetti code... give it time. I'm not
necessarily opposed to the idea (especially as a contrib module), but
I'm a little nervous that we might be overestimating the extent to
which Dean's needs are universal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 27 August 2011 02:32, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Frankly, our current date parsing code is pretty darn strange and
> flaky...

So Robert and Merlin both expressed concerns that the existing
datetime string parsing code is so complicated that adding to it would
likely just introduce more bugs.

My first thought was 'how hard can it be?' - famous last words :-)

Anyway I knocked up the attached POC patch implementing my originally
proposed syntax. I haven't tested it much, so it may well have bugs,
but the separation of the new code seems pretty clean, so it shouldn't
break any existing parsing logic.

Here are a few examples of what it allows:

SELECT str, str::timestamptz result FROM (VALUES
('2011-08-27'),
('today'),
('now'),
('today minus 5 days'),
('now plus 2 hours'),
('tomorrow plus 1 month'),
('minus 30 minutes'),
('25/12/2011 plus 6 weeks')
) AS x(str);

str | result
-------------------------+-------------------------------
2011-08-27 | 2011-08-27 00:00:00+01
today | 2011-08-27 00:00:00+01
now | 2011-08-27 12:11:46.245659+01
today minus 5 days | 2011-08-22 00:00:00+01
now plus 2 hours | 2011-08-27 14:11:46.245659+01
tomorrow plus 1 month | 2011-09-28 00:00:00+01
minus 30 minutes | 2011-08-27 11:41:46.245659+01
25/12/2011 plus 6 weeks | 2012-02-05 00:00:00+00
(8 rows)

(I decided not to implement 'Christmas plus three fortnights' ;-)

I don't have a feel for how widely useful this is, and I'm not
particularly wedded to this syntax, but if nothing else it has been a
fun exercise figuring out how the datetime string parsing code works.

Regards,
Dean

On 27 August 2011 12:29, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> ... if nothing else it has been a
> fun exercise figuring out how the datetime string parsing code works.
>

While looking through the current code, I spotted the following oddity:

select timestamp 'yesterday 10:30';
timestamp
---------------------
2011-08-26 10:30:00

which is what you'd expect, however:

select timestamp '10:30 yesterday';
timestamp
---------------------
2011-08-26 00:00:00

Similarly "today" and "tomorrow" reset any time fields so far, but
ISTM that they should really be preserving the hour, min, sec fields
decoded so far.

Regards,
Dean

On lör, 2011-08-27 at 12:29 +0100, Dean Rasheed wrote:
> So Robert and Merlin both expressed concerns that the existing
> datetime string parsing code is so complicated that adding to it would
> likely just introduce more bugs.
>
> My first thought was 'how hard can it be?' - famous last words :-)

Maybe you can find an existing Perl or Python module that has already
implemented this. Then the solution might be 5 lines of wrapping this
into a PostgreSQL function.

Greetings,

On Thursday, August 25, 2011 05:39:09 AM Dean Rasheed wrote:
> As background, I have an app that accepts user text input and casts it
> to a timestamp in order to produce reports. I use PostgreSQL's
> timestamp input conversion for this, since it gives a lot of
> flexibility, and can parse pretty much anything the users throw at it.
>
> It is also handy that it recognizes special case values like "now",
> "today", "tomorrow" and "yesterday". However, I can't see any way of
> entering more general relative timestamps like "5 days ago" or "2
> hours from now".
>

Years ago I wrapped 'getdate.y' from the CVS source code and made it into a
python extension. It handles "+2 hours" or "next week", etc. I don't know much
of anything about making pg contrib modules, but it should not be hard to do.
The way it works is you pass in a string and it returns the unix timestamp.

[...snipped...]
>
> Thoughts?
> Better ideas?
>
> Regards,
> Dean

Regards,
J

On 27 August 2011 14:14, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On lör, 2011-08-27 at 12:29 +0100, Dean Rasheed wrote:
>> So Robert and Merlin both expressed concerns that the existing
>> datetime string parsing code is so complicated that adding to it would
>> likely just introduce more bugs.
>>
>> My first thought was 'how hard can it be?' - famous last words :-)
>
> Maybe you can find an existing Perl or Python module that has already
> implemented this.  Then the solution might be 5 lines of wrapping this
> into a PostgreSQL function.
>

Ah now that's an interesting idea.

Python's dateutil module seems to come highly recommended, although I
don't find this too encouraging:

>>> dateutil.parser.parse('today', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)
>>> dateutil.parser.parse('tomorrow', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)
>>> dateutil.parser.parse('foobar', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)

Still, there might be something better out there...

Cheers,
Dean

On 27 August 2011 14:29, Jeff MacDonald <jam(at)zoidtechnologies(dot)com> wrote:
> Greetings,
>
> On Thursday, August 25, 2011 05:39:09 AM Dean Rasheed wrote:
>> As background, I have an app that accepts user text input and casts it
>> to a timestamp in order to produce reports. I use PostgreSQL's
>> timestamp input conversion for this, since it gives a lot of
>> flexibility, and can parse pretty much anything the users throw at it.
>>
>> It is also handy that it recognizes special case values like "now",
>> "today", "tomorrow" and "yesterday". However, I can't see any way of
>> entering more general relative timestamps like "5 days ago" or "2
>> hours from now".
>>
>
> Years ago I wrapped 'getdate.y' from the CVS source code and made it into a
> python extension. It handles "+2 hours" or "next week", etc. I don't know much
> of anything about making pg contrib modules, but it should not be hard to do.
> The way it works is you pass in a string and it returns the unix timestamp.
>
> [...snipped...]

That sounds like a pretty good approach, and a contrib module might
well be the way to go.

I'm not sure how best to handle timezones though, since it's
hard-coded list probably won't match the timezones PostgreSQL knows
about. Maybe that doesn't matter, I'm not sure.

Regards,
Dean

Greetings,

On Saturday, August 27, 2011 11:36:13 AM Dean Rasheed wrote:
>
> I'm not sure how best to handle timezones though, since it's
> hard-coded list probably won't match the timezones PostgreSQL knows
> about. Maybe that doesn't matter, I'm not sure.
>

It'll matter when the expression has a result that crosses the DST date. Does
Postgres have a library that could be used by the parser?

> Regards,
> Dean

Regards,
Jeff

On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 27 August 2011 12:29, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> ... if nothing else it has been a
>> fun exercise figuring out how the datetime string parsing code works.
>
> While looking through the current code, I spotted the following oddity:
>
> select timestamp 'yesterday 10:30';
>      timestamp
> ---------------------
>  2011-08-26 10:30:00
>
> which is what you'd expect, however:
>
> select timestamp '10:30 yesterday';
>      timestamp
> ---------------------
>  2011-08-26 00:00:00
>
> Similarly "today" and "tomorrow" reset any time fields so far, but
> ISTM that they should really be preserving the hour, min, sec fields
> decoded so far.

Sounds right to me. Want to send a patch?

BTW, this is exactly the sort of thing that makes me a bit skeptical
about further extending this...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 28 August 2011 00:39, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> On 27 August 2011 12:29, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>> ... if nothing else it has been a
>>> fun exercise figuring out how the datetime string parsing code works.
>>
>> While looking through the current code, I spotted the following oddity:
>>
>> select timestamp 'yesterday 10:30';
>>      timestamp
>> ---------------------
>>  2011-08-26 10:30:00
>>
>> which is what you'd expect, however:
>>
>> select timestamp '10:30 yesterday';
>>      timestamp
>> ---------------------
>>  2011-08-26 00:00:00
>>
>> Similarly "today" and "tomorrow" reset any time fields so far, but
>> ISTM that they should really be preserving the hour, min, sec fields
>> decoded so far.
>
> Sounds right to me.  Want to send a patch?
>

The attached patch makes "today", "tomorrow" and "yesterday" only set
the year, month and day fields. All the other fields are already
initialised to 0 at the start, and may be set non-zero before or after
encountering these special date values. The result should now be
independent of the order of the fields.

Regards,
Dean

On 28 August 2011 00:00, Jeff MacDonald <jam(at)zoidtechnologies(dot)com> wrote:
> Greetings,
>
> On Saturday, August 27, 2011 11:36:13 AM Dean Rasheed wrote:
>>
>> I'm not sure how best to handle timezones though, since it's
>> hard-coded list probably won't match the timezones PostgreSQL knows
>> about. Maybe that doesn't matter, I'm not sure.
>>
>
> It'll matter when the expression has a result that crosses the DST date. Does
> Postgres have a library that could be used by the parser?
>

On further examination of this and other datetime parsing code, I am
coming to the conclusion that for absolute timestamps the PostgreSQL
code is (IMO) the best and most flexible in terms of accepting a
variety of commons formats.

While getdate.y and others offer better support for relative
timestamps, it seems to come at the cost of poorer support for
absolute timestamps, which for me is a deal-breaker. There are also
other incompatibilities, such as different handling of "today", which
is just likely to lead to confusion.

It may not be worth the effort of trying to get a patch into core
PostgreSQL for this, but given the already excellent absolute
timestamp and interval support, I'm thinking that maybe the best
answer is to just write a userland function that breaks an input
string up into timestamp and interval parts and returns the resulting
timestamp.

Regards,
Dean

On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 28 August 2011 00:39, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>> On 27 August 2011 12:29, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>>> ... if nothing else it has been a
>>>> fun exercise figuring out how the datetime string parsing code works.
>>>
>>> While looking through the current code, I spotted the following oddity:
>>>
>>> select timestamp 'yesterday 10:30';
>>>      timestamp
>>> ---------------------
>>>  2011-08-26 10:30:00
>>>
>>> which is what you'd expect, however:
>>>
>>> select timestamp '10:30 yesterday';
>>>      timestamp
>>> ---------------------
>>>  2011-08-26 00:00:00
>>>
>>> Similarly "today" and "tomorrow" reset any time fields so far, but
>>> ISTM that they should really be preserving the hour, min, sec fields
>>> decoded so far.
>>
>> Sounds right to me.  Want to send a patch?
>
> The attached patch makes "today", "tomorrow" and "yesterday" only set
> the year, month and day fields. All the other fields are already
> initialised to 0 at the start, and may be set non-zero before or after
> encountering these special date values. The result should now be
> independent of the order of the fields.

OK, committed. Perhaps it should be back-patched, but since this was
only discovered during code-reading and not in the wild, I didn't
bother.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> The attached patch makes "today", "tomorrow" and "yesterday" only set
>> the year, month and day fields. All the other fields are already
>> initialised to 0 at the start, and may be set non-zero before or after
>> encountering these special date values. The result should now be
>> independent of the order of the fields.

> OK, committed. Perhaps it should be back-patched,

No, I don't think so. This is an incompatible behavioral change with a
small-but-not-zero probability of breaking existing applications.

regards, tom lane

On Tue, Aug 30, 2011 at 11:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>> The attached patch makes "today", "tomorrow" and "yesterday" only set
>>> the year, month and day fields. All the other fields are already
>>> initialised to 0 at the start, and may be set non-zero before or after
>>> encountering these special date values. The result should now be
>>> independent of the order of the fields.
>
>> OK, committed.  Perhaps it should be back-patched,
>
> No, I don't think so.  This is an incompatible behavioral change with a
> small-but-not-zero probability of breaking existing applications.

Well, I'm fine with not back-patching it, but think the existing
behavior is flat wrong. Having '04:00:00 yesterday' return midnight
yesterday is pretty well unjustifiable. An error would be reasonable,
and DWIM is reasonable, but anything else is the wrong answer. How
much worse would it have to be to qualify as a bug? What if we didn't
the hour, minute, and second at all, and returned a value based on
whatever garbage was left over in the relevant memory location? What
if we returned 40000 BC?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Well, I'm fine with not back-patching it, but think the existing
> behavior is flat wrong.

I'm not arguing that this way isn't better, just that it's different.
There have been zero user complaints about this behavior since Tom
Lockhart put it in, more than ten years ago. That sort of militates
against a hard-line "it's flat wrong" stance.

But more to the point, since there wasn't an error before and there
isn't an error now, this is just a silent behavioral change, and we
avoid doing those in released branches. People don't want to have to
retest their applications against minor releases.

regards, tom lane

On 30 August 2011 16:40, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> OK, committed.

Thanks.

I'm fine with not back-patching it, on the grounds given.

Cheers,
Dean