Thread: 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 <span dir="ltr"><<a href="mailto:dean.a.rasheed@gmail.com">dean.a.rasheed@gmail.com</a>></span>wrote:<br /><div class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">My first thought was to have some general way of adding or subtracting<br /> an interval at theend of an input timestamp, eg. by adding another<br /> couple of special values - "plus <interval>" and "minus <interval>".<br/> This would allow things like:<br /><br /> TIMESTAMPTZ 'today minus 5 days'<br /> TIMESTAMPTZ 'nowplus 2 hours'<br /></blockquote></div><br />Funny you should mention intervals...<br /><br />timestamptz 'today' - interval'5 days'<br />timestamptz 'now' + interval '2 hours'<br />
On 25 August 2011 10:43, Vik Reykja <vikreykja@gmail.com> wrote: > On Thu, Aug 25, 2011 at 11:39, Dean Rasheed <dean.a.rasheed@gmail.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.a.rasheed@gmail.com> wrote: > On 25 August 2011 10:43, Vik Reykja <vikreykja@gmail.com> wrote: >> On Thu, Aug 25, 2011 at 11:39, Dean Rasheed <dean.a.rasheed@gmail.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 feelup to writing it themselves, perhaps you would pay one of the consulting companies to do it for them? -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Fri, Aug 26, 2011 at 4:32 PM, Jim Nasby <jim@nasby.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'tfeel 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@gmail.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
Attachment
On 27 August 2011 12:29, Dean Rasheed <dean.a.rasheed@gmail.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@gmx.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@zoidtechnologies.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.a.rasheed@gmail.com> wrote: > On 27 August 2011 12:29, Dean Rasheed <dean.a.rasheed@gmail.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@gmail.com> wrote: > On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote: >> On 27 August 2011 12:29, Dean Rasheed <dean.a.rasheed@gmail.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
Attachment
On 28 August 2011 00:00, Jeff MacDonald <jam@zoidtechnologies.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.a.rasheed@gmail.com> wrote: > On 28 August 2011 00:39, Robert Haas <robertmhaas@gmail.com> wrote: >> On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote: >>> On 27 August 2011 12:29, Dean Rasheed <dean.a.rasheed@gmail.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@gmail.com> writes:
> On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed <dean.a.rasheed@gmail.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed <dean.a.rasheed@gmail.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@gmail.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@gmail.com> wrote: > OK, committed. Thanks. I'm fine with not back-patching it, on the grounds given. Cheers, Dean