Re: Select "todays" timestamps in an index friendly way

On 24 October 2018 at 07:14, Mike Rylander <mrylander(at)gmail(dot)com> wrote:
>
> On Tue, Oct 23, 2018 at 5:38 AM Lutz Horn <lutz(dot)horn(at)posteo(dot)de> wrote:
> > I am looking for a way to select all timestamps that are "today" in an
> > index friendly way. This select should not depend on the concrete value
> > of "today".
>
> Per TFM, https://www.postgresql.org/docs/10/static/datatype-datetime.html
> on table 8.13, you can use special input values:
>
> SELECT * FROM t WHERE ts >= 'today'::timestamp AND ts < 'tomorrow'::timestamp;

Of course, you'd need to be careful never to use that in a view or
even a PREPAREd statement. Those abbreviations are evaluated when the
query is parsed. In those cases, you'd just get the results for
whatever day you did CREATE VIEW or PREPARE.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services