Re: Inputting relative datetimes

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