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